Help with VBA

[decadence]

Hi, I am currently using a Vlookup but the problem is the static columns and my columns will change from time to time.

Sub Update()
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).row
    Application.ScreenUpdating = False
    Range("I2:I" & LastRow).FormulaR1C1 = "=VLOOKUP(C[-8],C[4]:C[7],2,FALSE)"
    Range("J2:J" & LastRow).FormulaR1C1 = "=VLOOKUP(C[-9],C[3]:C[6],3,FALSE)"
    Range("K2:K" & LastRow).FormulaR1C1 = "=VLOOKUP(C[-10],C[2]:C[5],4,FALSE)"
    Columns("I:K").Copy: Columns("I:K").PasteSpecial xlPasteValues
    Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    [m1].Resize(, 4).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

I would like to base the search criteria on an Array of Headers and step through the Array of Headers if Found in Row 1 or Row 2, if not then do nothing.
Then I would like to be able to Match 2 columns and return the value of the adjacent cell if found and if not then skip to the next cell rather N/A or 0 being returned.
Hope this makes sense. Thanks

From This
[TABLE="width: 500"]
<tbody>[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Header5[/TD]
[TD]Header6[/TD]
[TD]Header7[/TD]
[TD]Header8[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]A1[/TD]
[TD]10.1[/TD]
[TD]5.222[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]1.2[/TD]
[TD]1.2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]B2[/TD]
[TD]3.4[/TD]
[TD]5.4[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]1.3[/TD]
[TD]1.3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]D4[/TD]
[TD]7.6[/TD]
[TD]9.8[/TD]
[TD]270[/TD]
[/TR]
[TR]
[TD]D4[/TD]
[TD]1.4[/TD]
[TD]1.4[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To This
[TABLE="width: 500"]
<tbody>[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Header5[/TD]
[TD]Header6[/TD]
[TD]Header7[/TD]
[TD]Header8[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]10.1[/TD]
[TD]5.222[/TD]
[TD]180[/TD]
[TD][/TD]
[TD][/TD]
[TD]A1[/TD]
[TD]10.1[/TD]
[TD]5.222[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]3.4[/TD]
[TD]5.4[/TD]
[TD]180[/TD]
[TD][/TD]
[TD][/TD]
[TD]B2[/TD]
[TD]3.4[/TD]
[TD]5.4[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]1.3[/TD]
[TD]1.3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]D4[/TD]
[TD]7.6[/TD]
[TD]9.8[/TD]
[TD]270[/TD]
[/TR]
[TR]
[TD]D4[/TD]
[TD]7.6[/TD]
[TD]9.8[/TD]
[TD]270[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[decadence]

I have managed to put something together but it doesn't seem to be robust enough, Can some one help with this please.

    Dim Col1, Col2, Cel1, Cel2 As Range, LR As Long
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).row
  
    Set Col1 = Range("A2:A" & LR)
    For Each Cel1 In Col1
    
    If InStr(1, ([i1].Value), "Reference") > 0 Then Set Col2 = Range("I2:I" & LR)
    If InStr(1, ([j1].Value), "Reference") > 0 Then Set Col2 = Range("J2:J" & LR)
    If InStr(1, ([k1].Value), "Reference") > 0 Then Set Col2 = Range("K2:K" & LR)
    If InStr(1, ([l1].Value), "Reference") > 0 Then Set Col2 = Range("L2:L" & LR)
    If InStr(1, ([m1].Value), "Reference") > 0 Then Set Col2 = Range("M2:M" & LR)
    If InStr(1, ([n1].Value), "Reference") > 0 Then Set Col2 = Range("N2:N" & LR)
    If InStr(1, ([o1].Value), "Reference") > 0 Then Set Col2 = Range("O2:O" & LR)
    If InStr(1, ([p1].Value), "Reference") > 0 Then Set Col2 = Range("P2:P" & LR)
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each Cel2 In Col2
    If Cel1.Value = Cel2.Value Then
        Cel1.Offset(columnOffset:=1).Value = Cel2.Offset(columnOffset:=1).Value
        Cel1.Offset(columnOffset:=2).Value = Cel2.Offset(columnOffset:=2).Value
        Cel1.Offset(columnOffset:=3).Value = Cel2.Offset(columnOffset:=3).Value
                End If
            Next
        Next
    [i1].Resize(, 10).EntireColumn.Delete
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True

 

[JackDanIce]

Your screen shot and explanation are not very clear to understand without benefit of being able to see your PC screen. E.g. in your first post, you just used term Header, but second post it's finding a value of Reference (which isn't in your first post)

Your code is suggesting you're searching a sheet (what is the name of the sheet?) between I1:P1 for the value "Reference"
When you find it, you are looping through that column for a match to Cel2.Value - what is Cel2? It's not clear where you have declared it.
When you find a match you are copying the 3 columns next to Cel2 to 3 columns next to the matched cell, then test next row

Instead of looping, have you considered using a filter to match to value Cel2 and then copy the relevant data to the filtered rows?

Finally

Dim Col1, Col2, Cel1, Cel2 As Range, LR As Long

Creates variables where Col1, Col2 and Cel1 are variant types, Cel2 is a range type and LR is type Long.

If you do not us "As {data type}" it defaults to variant, I think you meant:

Dim Col1 as Range, Col2 as Range, Cel1 as Range, Cel2 As Range, LR As Long

?

 

[decadence]

Apologies for being unclear, but like i said in my last post i have managed to put it together.

Basically what I am trying to do is Match the cell values from the Header1 column and the Header5 (Reference) Column then if found return the value of Header 6 column to Header 2 column, 7 to 3 and 8 to 4. Because Header5 (Reference) column may move from time to time I can't really use Vlookup. My Sheet name varies too so I would use ActiveSheet in the code. Cel2 is the Values in the Range stated (I1:I & LR)etc...