VBA code: report value in a selected column

[IlyassB320]

(for clarity, see pics and code)

Hi,

Having these data:

Table 1 in "customers" sheet

Table 2 in "cars" sheet

I'm able to get the matching value of each row of "customers" to "cars" in a separate sheet "results".

However, I need to achieve 2 things:

1. Reporting in column A "results" sheet, the A column value of each respective row (therefore extracting this from the individual sheets of "customers" and "cars").
2. Having a similar table layout with headers denoting the respective columns of results
   1. Col A= Customer/Inventory
   2. Col B= Car
   3. Col C= Color
   4. Col D= Interior

I have been able to achieve up to this stage (pics) from the attached code

Sub GenerateTable()
    Dim selectedRows As Range
    Set selectedRows = ThisWorkbook.Sheets("customers").Range("B2:D9")
    Dim resultSheet As Worksheet
    On Error Resume Next
    Set resultSheet = ThisWorkbook.Sheets("results")
    On Error GoTo 0
    If resultSheet Is Nothing Then
        Set resultSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        resultSheet.Name = "results"
    End If
    resultSheet.Cells.Clear
    Dim carsSheet As Worksheet
    Set carsSheet = ThisWorkbook.Sheets("cars")
    Dim carsRange As Range
    Set carsRange = carsSheet.Range("B2:D13")
    Dim rng As Range
    Dim row As Range
    Dim found As Range
    Dim match As Boolean
    Dim lastRow As Long
    For Each row In selectedRows.Rows
        match = False
        For Each rng In carsRange.Rows
            If row.Cells(1, 1) = rng.Cells(1, 1) And row.Cells(1, 2) = rng.Cells(1, 2) And row.Cells(1, 3) = rng.Cells(1, 3) Then
                If match = False Then
                    lastRow = resultSheet.Cells(Rows.Count, 1).End(xlUp).row + 1
                    row.Copy resultSheet.Cells(lastRow, 1)
                    match = True
                End If
                rng.Copy resultSheet.Cells(lastRow + 1, 1)
                lastRow = lastRow + 1
            End If
        Next rng
           Next row
End Sub

[#2]

@IlyassB320 

My sense is that you're trying to accomplish something that can fairly readily be done with some of the new dynamic array functions. Or by using Power Query. That is to say, the VBA code is probably unnecessary. Excel has really remarkable abilities to extract data from tables according to specified criteria.

It's not really clear, though, what the desired results are. Could you back up and describe--in plain ol' English words-- what you're trying to do when you combine the customer data with the car data?

[#3]

hi @mathetes,

what I’m trying to achieve is reporting the missing values of columns A respective to each row in cars and customers, meaning the customer name for the orange colored rows and the inventory number for the green colored rows.

As you see from the image, for every entry in “result”, it only reports the car, color and interior.

[#4]

@IlyassB320 

I"m sorry. I am not following that description. Maybe somebody else can help