CODEWITHSUNDEEP
excelvba
Steven H
Steven H

Reputation: 11

Excel enter table based on matched row and matched column

Basically, I'm trying to create a "Data Entry" tab. I have two data validation entry boxes that withdraw data dynamically from a table. The first cell indexes based on persons last name (Table2[LAST]). The second cell indexes Table1[#HEADERS]. While these are all fine and dandy, I need to enter the worksheet cell where those two intersect and turn that intersected cell into data from a cell on my data entry sheet.

Cell "B2" on worksheet1 is Data Validation Type list with dropdown from Table2[Last] (In worksheet2)

Cell "C2" on worksheet1 is Data Validation Type list with dropdown from Table1[#HEADERS] (In worksheet2)

Cell "D2" on worksheet1 is Data Validation Type "Date" and is what will be pushed to the intersecting cell on worksheet2 when I push the button. The code below is stuff I've found and stuck together and I just can't figure out why it fails on the final line.

Sub Button5_Click()

    Dim wsInfo As Worksheet: Set wsInfo = Worksheets("worksheet2")
    Dim lookupRange As Range
    Dim matchval As Range
    Dim indexVar As Long
    Dim myVal As Variant
    Dim matchval2 As Range
    Dim lookuprange2 As Range

    Set matchval = Sheets("worksheet1").Range("B2")
    Set lookupRange = wsInfo.Range("Table2[LAST]")
    If Not Application.WorksheetFunction.Sum(lookupRange) = 0 Then
        indexVar = Range(Application.Index(lookupRange, Application.Match(matchval, lookupRange))).Row
    End If

    Set matchval2 = Sheets("worksheet1").Range("B3")
    Set lookuprange2 = wsInfo.Range("Table1[#HEADERS]")
    If Not Application.WorksheetFunction.Sum(lookupRange) = 0 Then
        columnVar = Range(Application.Index(lookupRange, Application.Match(matchval2, lookuprange2))).Column
    End If

    wsInfo.Cells(indexVar, columnVar) = Sheets("worksheet1").Cells(2, "D").Value
End Sub

If there's an easier method for the data validation list to just give a relative reference, I can use that. It would also account for duplicate last names.

Upvotes: 0

Views: 37

Answers (1)

Steven H
Steven H

Reputation: 11

Thanks to SJR for pointing me in the correct direction.

Sub Button5_Click()

    Dim wsInfo As Worksheet: Set wsInfo = Worksheets("worksheet2")
    Dim pltws As Worksheet: Set pltws = Worksheets("Data Entry Tab")
    Dim lookupRange As Range
    Dim myVal As Variant
    Dim lookuprange2 As Range

    'Set row value to look for
    matchval = pltws.Cells(2, "B").Value
    'Set column to look in
    Set lookupRange = wsInfo.Range("Table2[LAST]")
    'Set column value to look for
    matchval2 = pltws.Cells(2, "C").Value
    'Set row to look in
    Set lookuprange2 = wsInfo.Range("Table1[#HEADERS]")

    'Returns row (Relative to the actual range provided, not the worksheet) that data is found on
    indexVar = Application.Match(matchval, lookupRange, 0)
    'Returns column (Also relative to the range provided, not the worksheet) that the data is found in
    columnVar = Application.Match(matchval2, lookuprange2, 0)

    'Have to offset to account for actual tables position in the worksheet.
    wsInfo.Cells(indexVar + 3, columnVar + 3).Value = pltws.Cells(2, "D").Value

End Sub

Upvotes: 1

Related Questions