Search column headers and insert new column if header does not already exist using Excel VBA

  • I have a spreadsheet that is updated regularly. The user will update two columns on sheet(create) with container type (this is the header name) and the quantity, which will be transferred to sheet(Tracking). I am trying to figure out how to search sheet2(Tracking for existing headers (container types), if found then quantity will be updated within that column for the next available row. If header is not found, therefore a new column is added to the right with that new header name, as well.

  • Code
    1. Sub TrackB()Dim wsCreat As Worksheet: Set wsCreat = Sheets("Create")Dim wsTracking As Worksheet: Set wsTracking = Sheets("Tracking")Dim cl As Range, lastHCell As Range, header As Range, i As Integer, j As Integer, str As VariantWith wsTracking Set header = .[a1:xx1]: Set lastHCell = header.End(xlToRight) iLstRow = .[a10000].End(xlUp).Offset(1, 0).Row 'Update default data [A:D] .Range("A" & iLstRow) = Date For Each str In Array("L8", "C4", "G43") .Cells(iLstRow, i + 2) = wsCreat.Range(str): i = i + 1 Next 'add Column if not Match For Each cl In wsCreat.[B11:B37, E11:E37] Dim k: k = Application.Match(cl, header, 0) If IsError(k) And cl <> vbNullString Then _ lastHCell.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=True: _ Set lastHCell = lastHCell.Offset(0, 1): lastHCell.Value2 = cl Next cl 'Update input Data i = 5 Dim cell As Range: j = 1 For Each cell In wsCreat.[B11:B37,E11:E37].Cells If cell.Value2 <> vbNullString Then .Cells(iLstRow, Application.Match(cell, header, 0)) = wsCreat.[C10,F10].Offset(j, 0) j = j + 1 End If Next cellEnd WithEnd Sub

    Edited once, last by royUK: Add Code Tags, please use them in future when posting code to the Forum. Watch this to see how https://youtu.be/tlbM-IkMwRM ().

  • This is what I have so far. I'm able to capture the input on the tracking sheet, however, if there are any blank spaces between

    .Cells(iLstRow, Application.Match(cell, header, 0)) = wsCreat.[C10,F10].Offset(j, 0)

    It will not display the data