Coding a formula into VBA...

[BrianExcel]

Currently I am employing the following calculation in a cell within a workbook (the text is verbatim, including dashes, apostrophes, etc.).

=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,7,0)

The calculation references a range in an external workbook I am looking up from.

I am trying to get this coded into Excel, so that when I push a button it scrolls down the column and performs that calculation for each cell until the end of the data set.

Right now, I have it inserted into the button_click code as follows:

=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,7,0)

BUT, when I run it I get: "Run-time error '1004': Application-defined or object-defined error.

Can anyone advise what I am doing wrong?

 

[Greg Truby]

Well, you should be assigning this to the formula property of a range object. Your code should look something along the lines of:

range("a1").formula = "=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,7,0)"

 

[BrianExcel]

Now I get an error: 1004: Method 'Range' of Object '_Worksheet' failed.

Any other thoughts?

 

[BrianExcel]

Ok, so I was able to get it to work using your code literally as the following:

Range("I3").Formula = "=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,0)"

Now what I need it to do is to copy that formula down to each cell until the data set ends. Right now it enters the formula for cell I3, using B3 as a reference, but I need it to run down the whole column. Can you help?!

 

[elvgames]

use the 'cell' function with a 'for / next' loop, rather than the 'range' function:

for x = 1 to 100 ' (replace 100 with how many rows)

cell(x, 9).Formula = "=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,0)"

next x

' x = the row number
' 9 = column number (column I)

let me know if that helps. visit www.elvgames.com !

 

[Greg Truby]

We fill down column I as far as there is data in column B?

If so then:

Sub FillColumn()
    Dim lngLastRow As Long
 
    Let lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
 
    Range("I3:I" & lngLastRow).Formula = "=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,0)"
End Sub

 

[BrianExcel]

Ok, here is where I am as of now. I have an input box which specifies how many rows I want to perform the calculation for (instead of hardcoding).

Sub btnRECApproved_Click()
Dim rAll As Range, rTarget As Range
Dim iTarget As Integer

    iTarget = InputBox("Enter the number of rows you wish to perform the calculation for.", "How many rows?")
    
    For x = 1 To iTarget
     
        With Sheets("Renewal Savings")
            .Range(x, 9).Formula = "=VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,0)"
        End With
    Next x
End Sub

BUT, this is still giving me the same 1004: application-defined or user-defined error as before....

 

[BrianExcel]

Correct Greg....as long as there is data in Column B...using the code I referenced above if possible...

 

[BrianExcel]

Sorry, I keep figuring this stuff out before you guys have a chance to reply...

SO...now I have the formulas working. BUT, everything is referencing off of cell B3, instead of the offset value of the destination cell....thoughts?

 

[Greg Truby]

Grrr - I edited the code in my last post to use your formula, but somehow my post didn't go through. My ISP keeps dropping my connection. Most annoying.

Please see the edited code in my previous post. That should work for you. And no - I don't want to use the code you posted. Loops are inefficient. Why ask for user input if it's not necessary? And only the formula in the first row would be correct.