mardi 1 septembre 2015

Transfer Data using IF THEN statements from sheet 1 to 2

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.

Pic1

enter image description here

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