If then

carlbj · #1 (**permalink**) 08-19-04, 10:26

Hello everyone,

I have a simple question for you. If a column contains certain information I would like to move it to a different column.

I know it can be done in VBA but I'm just not sure how. I've tried but failed. I need some direction from the masters.

For example if column A contains "***" move it to column B.

Thank you very much
Carl

DavidCoutts · #2 (**permalink**) 08-19-04, 10:49

something like

Code:

Sub test()
Dim Lastrow As Long
Dim i As Integer

    Lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To Lastrow
        If Range("A" & i).Formula = "***" Then
            Range("B" & i).Formula = "***"
            Range("A" & i).Formula = ""
        End If
    Next i
End Sub

or do you want to insert a cell

Code:

Sub test2()
Dim Lastrow As Long
Dim i As Integer

    Lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To Lastrow
        If Range("A" & i).Formula = "***" Then
            Range("A" & i).Insert xlShiftToRight
        End If
    Next i
End Sub

HTH

Dave

carlbj · #3 (**permalink**) 08-19-04, 11:56

Thank you Dave for your quick reply. I may have miscommunicated my problem . Instead of the column I ment to say the cell.

For instance

I want to move the cells that have * before them to another cell in another column. All the other cells in the column can be left alone.

Thanks again,
Carl

I've included a sceen shot of what I'm talking about[IMG]c:/excel.bmp[/IMG]

DavidCoutts · #4 (**permalink**) 08-27-04, 11:31

ok sorry about the delay

Code:

Sub test2()
Dim Lastrow As Long
Dim i As Integer

    Lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To Lastrow
        If Left(Trim(Range("A" & i).Formula, 1) = "*" Then
            Range("A" & i).Insert xlShiftToRight
        End If
    Next i
End Sub

HTH

Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech