I would like to transfer data from sheet1 to sheet2 as indicated in the below pictures. I thought it best to use an if statement. For example, if a cell in the search range equals "RES", then place entire column containing "RES" on sheet2 starting at row 3 of the first empty column. ElseIF the next cell in the search range has a "B" in it, then place that entire column containing the cell with a "B" in it two columns from the "RES" column on sheet2, but place any other column with a "B" in it after the last column with a "B" in it on sheet2.
The code below will place the "RES" column starting at "A1" on sheet2 (I cannot figure out how to place it starting at row three). It will not transfer any columns with "B" in the header. It should be noted that the letter "B" need not always be in the first position of the string. Any help is much appreciated.
Code:
Sub TransferValues()
'If statement
Dim SrchRng As Range, cell As Range
Dim lc As Long
lc = Sheets("Sheet1").Cells(1, Columns.count).End(xlToLeft).Column
Set SrchRng = Sheets("Sheet1").Range("A1:A" & lc & "")
For Each cell In SrchRng
If InStr(1, cell.Value, "RES") > 0 Then 'works
Sheets("Sheet2").Range("A1").End(xlToLeft).Offset(3, 0).EntireColumn.Value = cell.EntireColumn.Value
ElseIf InStr(1, cell.Value, "A", vbTextCompare) > 0 Then 'does not work
Sheets("Sheet2").Range("A1").End(xlToLeft).Offset(0, 2).EntireColumn.Value = cell.EntireColumn.Value
End If
Next cell
Application.DisplayAlerts = True
End Sub
Aucun commentaire:
Enregistrer un commentaire