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

 

[steve the fish]

What i dont quite understand is why are you adding the workbook name and sheet name to the vlookup when you are already in that sheet?

 

[RockandGrohl]

Hi Steve,

The formula is placed in bd, which is in workbook "FacRep"

The data I'm looking up is in sheet "list" which is in workbook "ml"

Hope that helps.

 

[steve the fish]

Ok is the formula going in S3 down still? If im right this works but the column being returned is the wrong column (apart from the microsoft excel bit)? You want that column based on headers? Will the header always be within U1:EM1 of the list sheet?

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

 

[RockandGrohl]

Ok is the formula going in S3 down still? If im right this works but the column being returned is the wrong column (apart from the microsoft excel bit)? You want that column based on headers? Will the header always be within U1:EM1 of the list sheet?

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

Hi Steve,

So yeah the problem is that on the VBA side of things, it's not correctly telling excel that [Microsoft Excel] is in fact workbook "ml" with sheet "list"

If you'll recall, I had a similar issue which you and RoryA helped me with some months ago, here:

https://www.mrexcel.com/forum/excel...-make-vba-sumifs-reference-right-sheet-2.html

It involved this weird mix of XX.parent.name and certain characters to get it to read what where I wanted it to look for the location of the data.

 

[steve the fish]

So it's not that you need the correct column? You would just need ml.name if you wanted the name of workbook assigned to the variable ml

 

[RockandGrohl]

So it's not that you need the correct column? You would just need ml.name if you wanted the name of workbook assigned to the variable ml

Ah so instead of ml.parent.name, just ml.name?

I'll give it a go, cheers!


EDIT: just tried this, no dice so far:

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

Application-defined or object-defined error.

 

[RoryA]

You need to concatenate in the column, but you actually want the column number, not letter, since you're using R1C1 formulas.

 

[RockandGrohl]

You need to concatenate in the column, but you actually want the column number, not letter, since you're using R1C1 formulas.

Hi Rory, thanks for getting back to my PM.

Like this?

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

Addr8 = 21
pu = 143
VLrng = 123

So it should be looking at Vlookup(L3,[ml]!U:EM, 123, 0)

 

[RockandGrohl]

Might be useful if I paste everything from the code that's relevant here:

Workbooks.Open Filename:=facml, ReadOnly:=TrueSet ml = ActiveWorkbook
' Add in Concat
Dim Lastrowml As Long
Lastrowml = Cells(Rows.Count, "A").End(xlUp).Row
Range("U2:U" & Lastrowml).FormulaR1C1 = "=UPPER(RC13&IFERROR(LEFT(RC14,FIND("" "",RC14)-1),RC14)&IFERROR(REPLACE(RC22,FIND("" "",RC22),1,""""),RC22))"
Range("U2:U" & Lastrowml).Copy
Range("U2").PasteSpecial xlPasteValues
vol = Lastrowml - 1

'Grab Pickup
ml.Activate
Range("A1").Activate
ActiveSheet.Name = "list"
Set List = Worksheets("list")
Application.ScreenUpdating = False


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 = pu - Addr8 + 1




bd.Activate


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

Neither of the two codes work. Thanks!