VBA, using variables for all stages of Vlookup, from a changing source sheet.

[RockandGrohl]

Hello,

I have a file that I declare as "ml" with the renamed as "list"

This file has two fields I'm interested in, "Addr8" and "Pickup"

Unfortunately, these two columns change for each campaign so I can't reliably tell the Vlookup that Addr8 is and Pickup is [AA]

Here's what I have:

ml.ActivateRange("A1").Activate
ActiveSheet.Name = "list"




Do Until Cells(1, ActiveCell.Column).Value = "Address8"
ActiveCell.Offset(0, 1).Activate
Loop


Addr8 = ActiveCell.Column


Do Until Cells(1, ActiveCell.Column).Value = "Pickup"
ActiveCell.Offset(0, 1).Activate
Loop


PU = ActiveCell.Column


Dim AddColumnNumber As Long
Dim AddColumnLetter As String
Dim PUColumnNumber As Long
Dim PUColumnLetter As String


AddColumnLetter = Split(Cells(1, Addr8).Address, "$")(1)
PUColumnLetter = Split(Cells(1, PU).Address, "$")(1)


VLrng = AddColumnLetter - PUColumnLetter + 1




bd.Activate


Range("S3:S" & Lastrow).FormulaR1C1 = "=IF(RC[-6]=""Y"",IFERROR(VLOOKUP(RC[-7],'[" & ml.Parent.Name & "]list'!AddColumnLetter & : & PUColumnLetter,VLrng,0),""""),"""")"

So what I'm after is a formula placed in column S that Vlookups:

The value in RC-7 which is fine
Then it needs to look in the workbook I have open and declared as ml, with the data in worksheet "list", which I believe is '[" & ml.Parent.Name & "]list'!

I've already found Addr8 in column U (which is column 21) and Pickup in column EM (which is column 143)

143 subtract 21 plus 1 is 123 (VLrng), so the column index to return is VLrng


Unfortunately, running this results in the following being placed in Column S in bd:

=IF(M3="Y",IFERROR(VLOOKUP(L3,'[Microsoft Excel]list'!$U:$EM,123,0),""),"")

So it's just the first bit that's falling over, as it doesn't know where to look.

Any help would be appreciated, thanks!
- Chris

 

[RoryA]

It would be:

Range("S3:S" & lastrow).FormulaR1C1 = "=IF(RC[-6]=""Y"",IFERROR(VLOOKUP(RC[-7],[" & ml.Name & "]list!C" & Addr8 & ":C" & pu & ",VLrng,0),""""),"""")"

 

[RockandGrohl]

I'm still getting application defined or object-defined error (1004) on this, let me try running it from the top again, cheers!

 

[RockandGrohl]

Found it... Almost.

If I define Activeworkbook.name as "Nam" this now vlookups correctly:

Range("S3:S" & lastrow).FormulaR1C1 = "=IF(RC[-6]=""Y"",IFERROR(VLOOKUP(RC[-7],'[" & nam & "]list'!C" & Addr8 & ":C" & pu & ",123,0),""""),"""")"

You see that 123? If I swap it for Vlrng, it doesn't return anything. How do I get that to say 123 when it's actually entered in a cell, as opposed to "VLrng"?


EDIT: Solved, wasn't concatenating the range properly!

Range("S3:S" & lastrow).FormulaR1C1 = "=IF(RC[-6]=""Y"",IFERROR(VLOOKUP(RC[-7],'[" & nam & "]list'!C" & Addr8 & ":C" & pu & "," & VLrng & ",0),""""),"""")"