VBA, using variables for all stages of Vlookup, from a changing source sheet.
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA, using variables for all stages of Vlookup, from a changing source sheet.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Posts
    298
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 [U] and Pickup is [AA]

    Here's what I have:

    Code:
    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:

    Code:
    =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

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

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

    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?

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Posts
    298
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

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

    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),""),"")

  5. #5
    Board Regular
    Join Date
    Aug 2018
    Posts
    298
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by steve the fish View Post
    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-...t-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.

  6. #6
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

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

    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

  7. #7
    Board Regular
    Join Date
    Aug 2018
    Posts
    298
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by steve the fish View Post
    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:

    Code:
    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.
    Last edited by RockandGrohl; Jun 24th, 2019 at 04:53 AM.

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,679
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

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

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

  9. #9
    Board Regular
    Join Date
    Aug 2018
    Posts
    298
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by RoryA View Post
    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?

    Code:
    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)
    Last edited by RockandGrohl; Jun 25th, 2019 at 12:19 PM.

  10. #10
    Board Regular
    Join Date
    Aug 2018
    Posts
    298
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code:
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •