Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    getting macro to return a function in a given cell (excel 2000)

    I attempted to create a macro that will return a function to a specified cell. However I keep getting an error at the line which is typed below:

    ActiveCell.FormulaR1C1 = "=VLOOKUP($B21,[LersonalLDPERSONAL2.XLS]Ratings!$C$5:$AZ$500,2,FALSE) * VLOOKUP($B21,[LersonalLDPERSONAL2.XLS]Ratings!C5:AZ500,2,FALSE)"

    If this VLOOKUP command were entered straight into the worksheet it would return a certain number which is identified by the contents in cell B21. This formula is just taking that number then multiplying it to itself. However, this did not work when I typed it into the macro program as above. I just started using Visual Basics about a few weeks ago, so I don't know most of what the program can and cannot do. Will the program accept this argument or am I typing something wrong? If it doesn't accept this kind of argument how can I get this macro to do what I am trying to make it do?

    Thanks

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    Hi frogmand,

    I haven't studied your formula in detail, but if your going to use
    ActiveCell.FormulaR1C1
    then your formula needs to be in R1C1 format also. Try:
    ActiveCell.Formula
    instead.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    I tried it but I still get the same error. What else could I do?

    Thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    Try placing a single quote mark (') before each [ (openening square bracket) and each ! (exclamation mark).

    Andrew

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    <P ID="edit" class=small>(Edited by macropod on 29-Mar-03 06:48. Andrew's corrections added)</P>Hi frogmand,

    What type of error? Is your macro failing, or are you not getting the results you expect?

    I noticed too that your formula isn't quite the same in both parts,
    VLOOKUP($B21,[LersonalLDPERSONAL2.XLS]Ratings!$C$5:$AZ$500,2,FALSE) -vs-
    VLOOKUP($B21,[LersonalLDPERSONAL2.XLS]Ratings!C5:AZ500,2,FALSE)
    but, since you're squaring the answer, the formula could be simplified to:
    "=VLOOKUP($B21,'[LersonalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    Its a Run-time error '1004'. Its says "Application-defined or object-defined error". Heres a complete copy of the macro I created.

    Sub Macro2()

    Range("F28:G40").Select
    Selection.ClearContents
    a = 13
    b = 1
    c = 0
    d = 0
    Cells(13, 6).Select

    Do Until ActiveCell = ""
    ActiveCell.Offset(1, 0).Select
    d = d + 1
    Loop

    a = d - a

    Cells(13, 6).Select

    Do Until ActiveCell = ""
    If ActiveCell = "LineType" Then
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Cells(28 + a, 7).Select
    ActiveCell.Offset(1 + c, 0).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "LineType"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveCell.Formula = "=VLOOKUP($B21,[LersonalLDPERSONAL2.XLS]Ratings!$C$5:$AZ$500,2,FALSE)^2"
    Cells(13 + b, 6).Select
    b = b + 1
    c = c + 1
    Else: ActiveCell.Offset(1, 0).Range("A1").Select
    b = b + 1
    End If
    Loop

    Application.CutCopyMode = False

    End Sub

    This macro basically reads a range of strings and if the cell contains the word "LineType" it copy's the cell next to it and pasts it to another cell. The cell that contains the pasted character is the ID cell ($B21) used in the VLOOKUP command to get the appropriated value. The reason I have all of the counters in is because there are other rows underneath the row the macro starts on that has to go through the same process. The macro works find without the statement that has the VLOOKUP command in it. However, as soon as I run the macro with the statement in I get this Run-time Error.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    If I add single quotes to your formula as below, it seems to work:

    <pre> ActiveCell.Formula = "=VLOOKUP($B21,'[LersonalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"
    </pre>

    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    Hey Thanks! There is only one other thing. The $B21 statement inside the VLOOKUP command stays the same although I have it performing in different cells. So it only gives me the first value over and over no matter which cell its in. I know I told the macro to put this in the cell, however, how can I get it to change like it would if I just put the command directly onto to worksheet? Is there a way excel can work around this?

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    What do you want it to change to? Some cell relative to the cell where the formula is being stored? If so what is the relation to the cell where the formula is?
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    I would like it to change from$B21 to $B22 then to $B23 and so on (like creating $B21 then filling down). I actually have this VLOOKUP command applied to about five other rows coming down from row 21. There is a different character for each row and the B colunm. These characters are what the VLOOKUP command is using to get its values. So I made the macro to perform a calculation whose result depends on the character being used in the VLOOKUP command. However, I did not realize that the macro would only put in the cells exactly what I typed in the program. So instead of $B21 changing to $B22 and so on (to other rows beneath), each row stays the same (VLOOKUP only looks at $B21). Which means I only get the same value for each row. Take a look at the attachment for better clarification. If you can, try the macro (short-cut key ctrl-w).

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    I could not figure out what should go in column L from your workbook, but the macro below should do what you want for the other columns:

    <pre>Public Sub BuildLineType()
    Dim I As Long, J As Long
    I = 0
    J = 0
    With Worksheets("Sheet1")
    .Range("F28:G33").ClearContents
    Do While .Range("F13").Offset(I, 0).Value <> ""
    If .Range("F13").Offset(I, 0).Value = "LineType" Then
    .Range("F27").Offset(J, 0).Value = .Range("F13").Offset(I, 0).Value
    .Range("G27").Offset(J, 0).Value = .Range("G13").Offset(I, 0).Value
    .Range("K27").Offset(J, 0).Formula = "=VLOOKUP($G" & .Range("K27").Offset(J, 0).Row & _
    ",'[LersonalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"
    J = J + 1
    End If
    I = I + 1
    Loop
    End With
    End Sub
    </pre>

    Legare Coleman

  12. #12
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    This is exactly what I was trying to do. Dang you're good! I don't understand all of what you done just yet but it works like butta on momma's biscuits.

    Big Thanks

  13. #13
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    Hey Legare,

    Theres one other thing about the macro that I wrote before. The first DO UNTIL loop in the macro was used to count the number of data that the macro was looking at (starting from F13 on down). I used this number to shift the Line Impedance data down depending on whether knew lines would be added to the data above. I wrote that so the macro would always begin writing the Line Impedance data a set length below the last line of the above data. How do I incorporate this same idea to the macro you wrote?

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    What you were doing was not completely clear since you were clearing the fixed range G28:F33. See if this will work for you:

    <pre>Public Sub BuildLineType()
    Dim I As Long, J As Long, lFirstRow As Long, lLastRow As Long
    I = 0
    J = 0
    With Worksheets("Sheet1")
    lFirstRow = .Range("F12").End(xlDown).Row + 2
    lLastRow = .Range("F1").Offset(lFirstRow, 0).End(xlDown).Row - 1
    Range(.Range("F1").Offset(lFirstRow, 0), .Range("L1").Offset(lLastRow)).ClearContents
    Do While .Range("F13").Offset(I, 0).Value <> ""
    If .Range("F13").Offset(I, 0).Value = "LineType" Then
    .Range("F1").Offset(lFirstRow + J, 0).Value = .Range("F13").Offset(I, 0).Value
    .Range("G1").Offset(lFirstRow + J, 0).Value = .Range("G13").Offset(I, 0).Value
    .Range("K1").Offset(lFirstRow + J, 0).Formula = "=VLOOKUP($G" & .Range("K27").Offset(J, 0).Row & _
    ",'[LersonalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"
    J = J + 1
    End If
    I = I + 1
    Loop
    End With
    End Sub
    </pre>

    Legare Coleman

  15. #15
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting macro to return a function in a given cell (excel 2000)

    I can't believe it! If I knew just half of what you guys know (WMVPs) my job would be a whole lot easier.

    Thanks a great deal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •