Forum Discussion
VBA code: report value in a selected column
(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:
- 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").
- Having a similar table layout with headers denoting the respective columns of results
- Col A= Customer/Inventory
- Col B= Car
- Col C= Color
- 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
- mathetesSilver Contributor
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?
- IlyassB320Copper Contributor
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.
- mathetesSilver Contributor