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]

BUT, everything is referencing off of cell B3, instead of the offset value of the destination cell....thoughts?

The code I posted does this?!

 

[Greg Truby]

I'm signing off for the night. Let me know if the code I posted doesn't work for you and I'll check back tomorrow.

 

[BrianExcel]

Thanks Greg. I'll take a look...

 

[BrianExcel]

Greg, I can't thank you enough for your help.

One last thing (Your current code works great BTW). The last step I need to do is that IF the value of the destination cell (column I) winds up being "N/A", then I need to perform the exact same calculation but using column 42 in the vlookup instead of 30.

It sounds like this would be an IF/THEN statement, but let me know if you have thoughts on how to run things more efficiently...

 

[BrianExcel]

Anyone else have any thoughts?

 

[BrianExcel]

bump...

 

[Greg Truby]

Greg, I can't thank you enough for your help...

Quite welcome.

One last thing (Your current code works great BTW). The last step I need to do is that IF the value of the destination cell (column I) winds up being "N/A", then I need to perform the exact same calculation but using column 42 in the vlookup instead of 30.

It sounds like this would be an IF/THEN statement, but let me know if you have thoughts on how to run things more efficiently...

Not an IF/THEN in your code - an IF in your formula. Change the formula to read:

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

Note: I always assume 2007 or better unless a poster states specifically that he is using an older version of Excel. The above will fail for Excel 2003 or lower.

 

[BrianExcel]

Thanks Greg.

One more question. It looks like once I adjust the ranges the errors go away, but how would I adjust the formula you provided to account for IF the first column contains "N/A" (not the error, just text) THEN show column 42. This is what I tried but it's not working so far...

.formula =

"=IF(VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,0 = ""N/A""),VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,42,0))"

 

[Greg Truby]

When you do the VLOOKUP into column 30, it succeeds, but it returns the value of "N/A" - a text value that is in column 30?

 

[BrianExcel]

Hey Greg,

Sorry, I didn't get a chance to reply to this yesterday. I was able to get it work. I forgot that the first part of the formula is the condition and the second, third, fourth, etc., is the desired result.

The formula that made it work is:

=IF(VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,FALSE)= ""N/A"",VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,42,TRUE),VLOOKUP(B3,'FY12 Renewal Savings BB Data Report_SAB.xlsx'!June_2011_Data,30,FALSE))

Thanks again for all your help!