Macro/VBA for lookups

[carefreeant88]

Hi everyone,

After some help again, with regards to a Macro/VBA.

This might not even be achievable (I am very new to this), but hoping someone might be able to assist?

To explain, I have a Sales Workbook which contains multiple tabs. Each tab has new data pasted into it each morning, and so - as such - new columns are being added each day etc.

What I am desperately hoping to achieve, is the below. I have probably over-complicated the explanation here but hopefully that makes sense! In essence, I am needing to do a lookup between 2 different sheets on the workbook; but the exact layout of these sheets changes each morning, with new columns etc being automatically added. In other words, the 'Sales Data' and 'Updated Sales Data' columns aren't fixed, hence I am hoping to put a solution in whereby the workbook can search for the header of these columns, allowing it to therefore adjust accordingly.

1. On the sheet called 'Sales Analysis', look for the Column header 'Sales Data 1'.
2. If you find that, move down one cell (ie move to Z2 or AA2, or AB2 - ie row '2' of wherever the header 'Sales Data 1' is found
3. Do an xLookup (or any other viable solution). Take the product code (this is always in column B on the 'Sales Analysis' sheet, so B2, B3, B4 etc). Look for that product code on the tab called 'Reconciliation'
4. If you find that product code on the 'Reconciliation' sheet, then find the column with the header called 'UPDATED VALUE 1' and pull through the numerical value shown in that corresopnding cell, onto the 'Sales Data 1' cell on the 'Sales Analysis' sheet

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim updated As Range, salesData As Range, srcWS As Worksheet, desWS As Worksheet, v As Variant, i As Long, x As Variant
    Set srcWS = Sheets("Reconciliation")
    Set desWS = Sheets("Sales Analysis")
    Set updated = srcWS.Rows(1).Find("UPDATED DATA 1", LookIn:=xlValues, lookat:=xlWhole)
    Set salesData = desWS.Rows(1).Find("SALES DATA 1", LookIn:=xlValues, lookat:=xlWhole)
    v = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        x = Application.Match(v(i, 1), srcWS.Range("B:B"), 0)
        If Not IsError(x) Then
            desWS.Cells(i + 1, salesData.Column) = srcWS.Cells(x, updated.Column)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[carefreeant88]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim updated As Range, salesData As Range, srcWS As Worksheet, desWS As Worksheet, v As Variant, i As Long, x As Variant
    Set srcWS = Sheets("Reconciliation")
    Set desWS = Sheets("Sales Analysis")
    Set updated = srcWS.Rows(1).Find("UPDATED DATA 1", LookIn:=xlValues, lookat:=xlWhole)
    Set salesData = desWS.Rows(1).Find("SALES DATA 1", LookIn:=xlValues, lookat:=xlWhole)
    v = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        x = Application.Match(v(i, 1), srcWS.Range("B:B"), 0)
        If Not IsError(x) Then
            desWS.Cells(i + 1, salesData.Column) = srcWS.Cells(x, updated.Column)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thanks so much for this. Unfortunately, it hasn't quite achieved what I needed it to (that's completely my mistake, as I omitted a few bits in error). Sorry! I will re-write what I need to achieve in my next reply - hopefully it's do-able (thanks and apologies again)

 

[carefreeant88]

Thanks so much for this. Unfortunately, it hasn't quite achieved what I needed it to (that's completely my mistake, as I omitted a few bits in error). Sorry! I will re-write what I need to achieve in my next reply - hopefully it's do-able (thanks and apologies again)

Ok so below is actually what needs to happen for it to work:

1. Search on the sheet called 'Buy Plan Sheet'
2. If you find the Column Header 'Demand 1' then move into the cell underneath that header (ie Row 2) of that column. Do an xLookup between the Product Code (always found in Column 'B' so B2, B3, B4 etc) against the worksheet called 'Reconciliation 3'. If you find that product code on that sheet, then pull through the corresponding values shown in column 'B' (the value I need will always be fixed in Column 'B' on the 'Reconciliation 3' sheet)
3. Search on the sheet called 'Buy Plan Sheet'
4. If you find the Column Header 'Orders 1' then move into the cell underneath that header (ie Row 2) of that column. Do an xLookup between the Product Code (always found in Column 'B' so B2, B3, B4 etc) against the worksheet called 'Reconciliation 1'. When looking on the sheet called 'Reconciliation 1', if you find that Product Code, then move to the column with the header 'Quantity', and pull through the corresponding value for that code (the difference between this one and the one above, is that the column header will always change position on the Reconciliation 1 sheet. In other words, that column won't be fixed, hence I need the code to be able to search for the specific header before pulling through the value).

Appreciate that is a lot of variables and might be a bit ambitious - but any help would be welcome!

Thanks

 

[Alex Blakenburg]

Just to keep it moving for when Mumps is back online, in your point 2 the lookup return column can't be column B if that is the column that contains the Product Code. What is the fixed column that contains the value to return from "Reconciliation 3" ?

 

[mumps]

Waiting for a response to @Alex Blakenburg 's question in Post #5.

 

[carefreeant88]

Apologies - you are absolutely right. The Product Code will always be in column 'A' on the Reconciliation 3 Sheet, but the data that I need to pull through will always be in column 'B' on that sheet.

 

[mumps]

This macro assumes that the codes in Reconciliation 1 are in column B.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim orders1 As Range, demand1 As Range, quantity As Range, recon1 As Worksheet, recon3 As Worksheet, desWS As Worksheet, v As Variant, i As Long, x As Variant
    Set desWS = Sheets("Buy Plan Sheet")
    Set recon1 = Sheets("Reconciliation 1")
    Set recon3 = Sheets("Reconciliation 3")
    Set demand1 = desWS.Rows(1).Find("Demand 1", LookIn:=xlValues, lookat:=xlWhole)
    Set orders1 = desWS.Rows(1).Find("Orders 1", LookIn:=xlValues, lookat:=xlWhole)
    Set quantity = recon1.Rows(1).Find("Quantity", LookIn:=xlValues, lookat:=xlWhole)
    v = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        x = Application.Match(v(i, 1), recon3.Range("A:A"), 0)
        If Not IsError(x) Then
            desWS.Cells(i + 1, demand1.Column) = recon3.Range("B" & x)
        End If
        x = Application.Match(v(i, 1), recon1.Range("B:B"), 0)
        If Not IsError(x) Then
            desWS.Cells(i + 1, orders1.Column) = recon1.Cells(x, quantity.Column)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[carefreeant88]

This macro assumes that the codes in Reconciliation 1 are in column B.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim orders1 As Range, demand1 As Range, quantity As Range, recon1 As Worksheet, recon3 As Worksheet, desWS As Worksheet, v As Variant, i As Long, x As Variant
    Set desWS = Sheets("Buy Plan Sheet")
    Set recon1 = Sheets("Reconciliation 1")
    Set recon3 = Sheets("Reconciliation 3")
    Set demand1 = desWS.Rows(1).Find("Demand 1", LookIn:=xlValues, lookat:=xlWhole)
    Set orders1 = desWS.Rows(1).Find("Orders 1", LookIn:=xlValues, lookat:=xlWhole)
    Set quantity = recon1.Rows(1).Find("Quantity", LookIn:=xlValues, lookat:=xlWhole)
    v = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        x = Application.Match(v(i, 1), recon3.Range("A:A"), 0)
        If Not IsError(x) Then
            desWS.Cells(i + 1, demand1.Column) = recon3.Range("B" & x)
        End If
        x = Application.Match(v(i, 1), recon1.Range("B:B"), 0)
        If Not IsError(x) Then
            desWS.Cells(i + 1, orders1.Column) = recon1.Cells(x, quantity.Column)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thank you!

 

[mumps]

You are very welcome.