Posts by Rey Taino

    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

    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.