+ Reply to Thread
Results 1 to 4 of 4

VBA VLOOKUP using cell references

  1. #1
    Registered User
    Join Date
    01-10-2023
    Location
    USA
    MS-Off Ver
    Microsoft 365 Version 2211
    Posts
    4

    VBA VLOOKUP using cell references

    I'm trying to find a value in a particular column. The code that I use is

    Match_Row = Application.WorksheetFunction.Match("460", ws.Range("AB:AB"), 0)

    The range changes as the code runs so I can't use a fixed range so using "AB:AB" won't work. So when the first part of the code finds a cell with a predefined background color and has a value greater than 0 in range "Range(Cells(Z_Row, X_Col), Cells(Z_Row, Y_Col))" then it then searches in AB for the matching cell value that is in "A" & activerow.

    Since my loop ends with X_Col = X_Col + 1, I need a Match or Vlookup code that also can have the same sequence increase of X_Col

    I hope I explained properly.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: VBA VLOOKUP using cell references

    Please attach a sample workbook. You probably will want to use Excel Tables since they "know" how big they are.

    The VBA command you want to use is FIND.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-10-2023
    Location
    USA
    MS-Off Ver
    Microsoft 365 Version 2211
    Posts
    4

    Re: VBA VLOOKUP using cell references

    As the actual workbook has sensitive information such employee names and numbers, I can't attach the actual workbook, here is the code.

    I was able to make it work, but not how I wanted. Had to put way too many ElseIf statements extending the code execution.

    Sub Arrows()
    Range("AB2:AH189").Interior.ColorIndex = -4142
    Dim dleft1 As Double, dleft2 As Double
    Dim dtop1 As Double, dtop2 As Double
    Dim dheight1 As Double, dheight2 As Double
    Dim dwidth1 As Double, dwidth2 As Double
    Dim FromRange As Range, ToRange As Range
    Dim End_Row As String, Job_Num As String, Match_Row As String
    Dim ws As Worksheet, cell As Range, rng2 As Range
    Set ws = Worksheets("EE Schedule")
    Cell_Color_2 = RGB(248, 203, 173)
    Start_Col = 28
    L_Col = 34
    Arrow_Col = 9
    End_Col = 9
    For x = 1 To 7
    Z_Row = ActiveCell.Row
    Z_Col = ActiveCell.Column
    If Z_Col = "8" Then
    Set rng2 = Range(Cells(Z_Row, Arrow_Col), Cells(Z_Row, Arrow_Col + 6))
    For Each cell In rng2
    Job_Num1 = Cells(Z_Row, 1).Value
    If cell.Column = 9 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AB:AB"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    ElseIf cell.Column = 10 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AC:AC"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    ElseIf cell.Column = 11 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AD:AD"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    ElseIf cell.Column = 12 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AE:AE"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    ElseIf cell.Column = 13 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AF:AF"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    ElseIf cell.Column = 14 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AG:AG"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    ElseIf cell.Column = 15 And cell.Interior.Color = Cell_Color_2 And cell.Value > 0 Then
    Match_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("AH:AH"), 0)
    Z_Row = Match_Row
    Z_Row = CInt(Z_Row)
    End_Row = Application.WorksheetFunction.Match(Job_Num1, ws.Range("A:A"), 0)
    Arrow_Col = cell.Column
    End_Col = cell.Column
    GoTo C_Next
    End If
    Next
    Job_Num = Application.WorksheetFunction.CountA(ws.Range(Cells(Z_Row, Start_Col), Cells(Z_Row, L_Col)))
    Job_Num1 = Range(Cells(Z_Row, Start_Col), Cells(Z_Row, Start_Col))
    If Job_Num > 0 Then
    If Job_Num1 = "B" Then
    End_Col = Start_Col - 9
    End_Row = Z_Row
    GoTo C_Next
    End If
    Start_Check = Cells(Z_Row, Start_Col).Value
    If Start_Check = "" Then
    GoTo G_Next
    Else:
    On Error GoTo ErrorHandler
    End_Row = Application.WorksheetFunction.Match(Start_Check, ws.Range("A:A"), 0)
    C_Next:
    Set FromRange = Cells(Z_Row, Arrow_Col)
    Set ToRange = Cells(End_Row, End_Col)
    dleft1 = FromRange.Left
    dleft2 = ToRange.Left
    dtop1 = FromRange.Top
    dtop2 = ToRange.Top
    dheight1 = FromRange.Height
    dheight2 = ToRange.Height
    dwidth1 = FromRange.Width
    dwidth2 = ToRange.Width
    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, dleft1 + dwidth1 / 2, dtop1 + dheight1 / 2, dleft2 + dwidth2 / 2, dtop2 + dheight2 / 2).Select
    With Selection.ShapeRange.Line
    .BeginArrowheadStyle = msoArrowheadNone
    .EndArrowheadStyle = msoArrowheadOpen
    .Weight = 4.75
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0.7
    End With
    End If
    End If
    End If
    G_Next:
    Start_Col = Start_Col + 1
    Arrow_Col = Arrow_Col + 1
    End_Col = Arrow_Col
    Job_Num = ""
    Next x
    Cells(Z_Row, Z_Col).Activate
    GoTo No_Error
    ErrorHandler:
    Cells(Z_Row, Start_Col).Interior.Color = vbYellow
    No_Error:
    Start_Col = 28
    End Sub

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: VBA VLOOKUP using cell references

    First of all, as a moderator will tell you, you should use code tags.

    If it's a single data table, then the link below should work for you.

    https://www.excelforum.com/tips-and-...tize-data.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using Vlookup to two different cell references
    By Mdoherty1967 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2014, 01:22 AM
  2. Copy cell contents between tabs provided cell references match (VLOOKUP?)
    By jparnell8839 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2014, 09:05 AM
  3. [SOLVED] Using VLOOKUP with cell references
    By JamesFletcher in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2013, 05:38 AM
  4. [SOLVED] VLOOKUP Error when lookup value references a cell that is a sum.
    By Joeg101 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-31-2013, 01:36 PM
  5. vlookup of multiple references in same cell
    By darthshani in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-11-2009, 01:17 PM
  6. vlookup using absolute cell references in VBA
    By Colin Vicary in forum Excel General
    Replies: 3
    Last Post: 01-23-2007, 09:45 AM
  7. vlookup with linked cell references
    By JAB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2006, 01:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1