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

Topic

New Reply

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,[L:personalLDPERSONAL2.XLS]Ratings!$C$5:$AZ$500,2,FALSE) * VLOOKUP($B21,[L:personalLDPERSONAL2.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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

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

Thanks

Reply | Quote

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

Andrew

Reply | Quote

(Edited by macropod on 29-Mar-03 06:48. Andrew’s corrections added)

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,[L:personalLDPERSONAL2.XLS]Ratings!$C$5:$AZ$500,2,FALSE) -vs-
VLOOKUP($B21,[L:personalLDPERSONAL2.XLS]Ratings!C5:AZ500,2,FALSE)
but, since you’re squaring the answer, the formula could be simplified to:
“=VLOOKUP($B21,'[L:personalLDPERSONAL2.XLS]Ratings’!$C$5:$AZ$500,2,FALSE)^2”

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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,[L:personalLDPERSONAL2.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.

Reply | Quote

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

    ActiveCell.Formula = "=VLOOKUP($B21,'[L:personalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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).

Reply | Quote

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:

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 & _
                  ",'[L:personalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"
                J = J + 1
            End If
            I = I + 1
        Loop
    End With
End Sub

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

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 & _
                  ",'[L:personalLDPERSONAL2.XLS]Ratings'!$C$5:$AZ$500,2,FALSE)^2"
                J = J + 1
            End If
            I = I + 1
        Loop
    End With
End Sub

Reply | Quote

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.

Reply | Quote