Using variable (cell address) in Vlookup

[ElmoZina]

Dear experts,

I hope you can assist me.
I am using a variable called "MyCell", it contains the address of the cell 3 columns left of the activecell.
This address is later on being used in the vlookup formula's however the outcome produces an error.

When I use the Activecell.formula statement the formula on the spreadsheet looks like this:
=IF(VLOOKUP(I10,'[Approved.xls]Page 1'!C1:C31,31,FALSE)="pending","Pending "&VLOOKUP(I10,'[Pending Approval.xls]Page 1'!C1:C29,29,FALSE),VLOOKUP(I10,'[Approved.xls]Page 1'!C1:C31,31,FALSE))
This produces an #N/A error

When I change the below code ActiveCell.Formula to ActiveCell.FormulaR1C1 the formula looks like this:
=IF(VLOOKUP('I10','[Approved.xls]Page 1'!$A:$AE,31,FALSE)="pending","Pending "&VLOOKUP('I10','[Pending Approval.xls]Page 1'!$A:$AC,29,FALSE),VLOOKUP('I10','[Approved.xls]Page 1'!$A:$AE,31,FALSE))
This produces the #NAME error as there are single quotes around the cell address e.g 'I10'
Are you able to provide an solution?
Many thanks.

Sub DailyOps()
Dim MyCell As String

MyCell = Replace((ActiveCell.Offset(0, -3).Address), "$", "")
ActiveCell.Formula = _
        "=IF(VLOOKUP(" & MyCell & ",'[Approved.xls]Page 1'!C1:C31,31,FALSE)=""pending"",""Pending ""&VLOOKUP(" & MyCell & ",'[Pending Approval.xls]Page 1'!C1:C29,29,FALSE),VLOOKUP(" & MyCell & ",'[Approved.xls]Page 1'!C1:C31,31,FALSE))"
    
End Sub

 

[edmitchell]

Hi

the VLOOKUP function, looks in a table of columns, but you are referencing "C1:C31" which is a series of rows, which is why the VLOOKUP fails and then the whole formula. If you are intending to look across 31 columns, then the table should be in the form of "C1:AG1" (which is 31 columns).

Perhaps you could confirm if this is the problem, or perhaps clarify further

 

[ElmoZina]

Hi,
No its not the problem, the single quotes that have been added around the cell address ('I10') when the script has ran is the problem.
When I use the same vlookup (not using vba) directly in a cell those single quotes are not being added.
Maybe something with the MyCell variable but I can't find it.

 

[edmitchell]

Hi

perhaps you could provide some example/sample data from the Approved.xls file (a couple of rows, "sanitised" as needed)? - (having changed the 'C1:C31' reference in the vlookup to 'C1:AG31' within the code above, and with my sample Approved file, the code is working fine and returning values, etc)

 

[ElmoZina]

By the way, the formula should look like this in the respective cell:
=IF(VLOOKUP(I10,'[Approved.xls]Page 1'!$A:$AE,31,FALSE)="pending","Pending "&VLOOKUP(I10,'[Pending Approval.xls]Page 1'!$A:$AC,29,FALSE),VLOOKUP(I10,'[Approved.xls]Page 1'!$A:$AE,31,FALSE)).
As you can see there are no single quotes round the cell address A10.

 

[FormR]

Hi, try like this:

ActiveCell.FormulaR1C1 = _ 
       "=IF(VLOOKUP(RC[-3],'[Approved.xls]Page 1'!C1:C31,31,FALSE)=""pending"",""Pending ""&VLOOKUP(RC[-3],'[Pending Approval.xls]Page 1'!C1:C29,29,FALSE),VLOOKUP(RC[-3],'[Approved.xls]Page 1'!C1:C31,31,FALSE))"

 

[edmitchell]

sorry - I presume you meant cell address 'I10' (rather than A10)? I've copied the above formula into a cell, and having entered into cell I10 a value in my sample approved file, the formulae correctly returns a value. So, perhaps you could paste some sample data from your approved file, and I'll take a further look?

 

[ElmoZina]

That worked, however I was trying to use a variable named "MyCell" (which stored the cell address) to replace the RC[-3] reference.
The variable MyCell is later also being used in other parts of the code.

 

[FormR]

Hi, then the easiest way would be to exclusively use A1 style.

Sub DailyOps()
Dim MyCell As String
MyCell = "I10"
ActiveCell.Formula = "=IF(VLOOKUP(" & MyCell & ",'[Approved.xls]Page 1'!$A:$AE,31,FALSE)=""pending"",""Pending ""&" _
  & "VLOOKUP(" & MyCell & ",'[Pending Approval.xls]Page 1'!$A:$AC,29,FALSE),VLOOKUP(" & MyCell & ",'[Approved.xls]Page 1'!$A:$AE,31,FALSE))"
End Sub

Make sure you use .Formula not .FormulaR1C1.

 

[ElmoZina]

Sorry for the very late response, that did the trick.
Thanks so much.