Posts by manfredm

    Good Day,
    I am selecting an arbitrary range via an InpuBox.
    i.e.: Set rng = Application.InputBox(prompt:="Select a Range", Type:=8)
    I need to extract the starting row number and the column number of that random selection.
    The row number I got easily, and for the column I managed to extract the 'Name', i.e. the letter "E". How to I convert this into the column 'number', i.e. 5?
    I have found a rather clumsy way:
    Select Case col
    Case "A"
    c = 1
    Case "B"
    c = 2
    Case "C"
    c = 3
    and so on.....


    This results in rather long code and I would like to improve/simplify on this.
    Any suggestions, please?


    Manfred

    I want to choose the area to copy, thus I use the following:


    Sub CopyRangeAsPic()
    sel = InputBox("Enter the Range in 'A1 : D5' Format", "Enter the Cell Range You Want to Copy")
    ActiveSheet.Range("" & sel & "").copypicture xlScreen, xlPicture
    End Sub



    HTH, Manfred

    I use:


    Sub FullPathFooter()
    For Each sht In ActiveWorkbook.Sheets
    Ampersand = Chr(38)
    Quote = Chr(34)
    FooterFormat = "Arial"
    FooterTextSize = "16"
    FooterText = ActiveWorkbook.FullName
    Formatting = Ampersand & Quote & FooterFormat & Quote & Ampersand & FooterTextSize
    FOOTER = Formatting & FooterText
    sht.PageSetup.LeftFooter = FOOTER
    Next sht
    End Sub


    HTH, Manfred

    The following works on my system with O2k on Win2K:


    Sub AutoOpen()
    Dim xlapp As Object
    Dim CheckReqeust As Object
    Set xlapp = CreateObject("Excel.Application")
    Set CheckRequest = xlapp.Workbooks.Add("c:\temp\prc.xlt")
    xlapp.Visible = True
    Set CheckRequest = Nothing
    Set xlapp = Nothing
    End Sub


    I have placed the code not into a module but into "ThisDocument"


    Regards, Manfred

    Hi Ben,
    2 questions please:
    -you say "...tab into the combobox" . What do you want to happen when you 'tab into it'?
    -how do you want to 'trigger' your date-entry in Col D ? I assume that for each row of parts you may have to go back at some stage and re-selct from Col C. Do you want to update your date at that point or...?


    Regards, Manfred

    I have a (tested) solution for you, where the font is based on a reference cell (i.e. A1)
    Sub Change_Text2()
    ActiveSheet.Shapes.SelectAll
    With Selection.Characters.Font
    .Name = Range("a1").Font.Name
    .FontStyle = Range("a1").Font.FontStyle
    .Size = Range("a1").Font.Size
    .ColorIndex = Range("a1").Font.ColorIndex
    End With
    Range("a1").Select
    End Sub


    If you require, you can add more font properties into the 'with' - 'end with' section, such as
    .Strikethrough =
    .Superscript =
    .Subscript =
    .OutlineFont =
    .Shadow =
    .Underline =


    HTH, Manfred

    if you want a bit more flexibility with regards to the sheetnames and 'wildcards' you use you could use the following (tested) macro:


    Sub DelSheets()
    Dim SheetNames() As String
    Dim SheetCount As Integer
    Dim i As Integer
    Dim wildcard As String
    Dim length As Integer


    wildcard = InputBox("Type in 'wildcard' characters for the sheets you want to delete", "Deletion of Worksheets")
    length = Len(wildcard)


    Application.DisplayAlerts = False
    SheetCount = ActiveWorkbook.Sheets.Count
    ReDim SheetNames(1 To SheetCount)


    For i = 1 To SheetCount
    SheetNames(i) = ActiveWorkbook.Sheets(i).Name
    Next i


    For i = 1 To SheetCount
    If Left(SheetNames(i), length) = wildcard Then
    Worksheets(SheetNames(i)).Delete
    End If
    Next i
    Application.DisplayAlerts = True
    End Sub



    Best Regards, Manfred

    from your request it is not entirely clear how you want to print ALL formulas.
    I assume you are referring to formulas in one of your worksheets/books.


    a) you could switch to 'formula view' as described in the posting from Hans or, a slightly faster way is to use Ctrl+~. Once you see your sheet with the formulas (and unformatted numbers) you can print it.


    b) if you want to search your document for formulas only (via VBA) and then print out all the formulas in a listing (on a newly created sheet) you could use the attached macro.
    First select a range you want to investigate (or the whole sheet), then run macro.


    Sub ListFormulas()
    Dim counter As Integer
    Dim i As Variant
    Dim sourcerange As Range
    Dim destrange As Range
    Dim curSheet As String
    curSheet = ActiveSheet.Name
    Set sourcerange = Selection.SpecialCells(xlFormulas)
    Sheets.Add
    ActiveSheet.Name = "test"
    Set destrange = Sheets("test").Range("b1") 'Substitute your range here
    destrange.CurrentRegion.ClearContents
    destrange.Value = "Address"
    destrange.Offset(0, 1).Value = "Formula"
    Sheets(curSheet).Activate
    If Selection.Count > 1 Then
    For Each i In sourcerange
    counter = counter + 1
    destrange.Offset(counter, 0).Value = i.Address
    destrange.Offset(counter, 1).Value = "'" & i.Formula
    Next
    ElseIf Selection.Count = 1 And Left(Selection.Formula, 1) = "=" Then
    destrange.Offset(1, 0).Value = Selection.Address
    destrange.Offset(1, 1).Value = "'" & Selection.Formula
    Else
    MsgBox "This cell does not contain a formula"
    End If
    destrange.CurrentRegion.EntireColumn.AutoFit
    End Sub


    HTH, Manfred

    Hi there,
    The spreadsheet U just sent through has a number of problems built into the 'green' area (c16:n18). Because U moved the 'yellow' area from (c6:n8) to (c8:n10) the formulas in (d16:n18) are refering to the wrong area, thus the results are wrong. In the attached sheet (cashflow3.xls) I have fixed this.


    If you check the 'light blue' area (c35:n35) you will see that the results you want (in this instance for Plat) are EXACTLY the values I already had in my previous sheet - as well as in this sheet (c16:n16).


    If you analyse the formulas ('green' area) you will notice that they are already accumulative, i.e. they do exactly what you are doing with your table in c22:n33.
    Please refer to the colored-in areas in the attached sheet for further explanation.


    Any hassels, pls ask.


    Best regards, Manfred

    Hi There,
    In the attached sheet pls check the following:
    - the green range (C17:N20) contains the formulas you are after. You just need to multiply the monthly fees for each month and ADD the fees from the previous month
    - there are a number of errors in your formulas, pls check the areas highlited in orange. I have fixed them, but pls check.


    Any further questions, please contact me.
    Regards, Manfred

    Hi there,
    it took me quite a while to get this one right.
    The main complication was that there are 2 off '0'-values at the bottonm right of the table and that created all sort of problems when interpolating close to the max power or max speed range.
    However, I think I ironed out those bugs and the model -seems to- work fine. Any hassels, let me know.
    Have fun,
    Manfred

    I have attached your spreadsheet with the solution to Question 1.


    Q2 is much more elaborate as you have non-linear relationships between pulley diameter and shaft speed as well as pulley diameter and design power.


    I'll have a look at it anyhow.


    Regards, Manfred

    hi itsmechang,
    Sorry to sound 'thick' but there are a few things I still don't understand:
    -In your first post you refer to a 'setup fee' and an 'ongoing monthly payment'. I can not see in the worksheet where you capture 'setup fees' and where the 'monthly cost'. I would assume that those costs are different and thus need a seperate place to be captured.
    - in your model I can not see any facility that allows to see/track/capture when someone joins or how many joined in one month
    -if, in your revenue section c6:c8, there are 1Plat, 1Gold, 1Silver unit, does this mean that 3 have joined in that month?
    - your section 'unit cost': does it refer to either 'setup fee', 'monthly cost', or both or something else?
    - in your last posting: could you please elaborate on the example of 3 people joining.. What are all the '100' - I assume they are not 'unit costs' as for Gold it would be 75 and for Silver=50
    - in the same example, how can one see who (i.e. how many) signs up for what and when?
    - how do you arrive at the 100-200-300-300 set of numbers?


    Sorry for the many questions, but I would like to understand in order to be of help.
    Regards Manfred