moving contents of cells based on criteria

[scottwilsonx]

Hope someone can help with this question:

I have a range of cells between A10:B16.

Based on the information in B10 to B16 (ie in column B) I want a macr
to take the values and drop them into cells A4 to A9 respectively

For example: If b10 does not contain "(A1)" then the value of B10 i
dumped into A5. If B11 does not contain "(A1)" then the value of B1
is dumped into A6 etc.


Is this possible using a loop of some sort ?

Many thanks for your help.

Scot

 

[Bernie Deitrick]

Scott,

Sub TryNow()
Dim myCell As Range
Dim i As Integer

Range("A4:A9").ClearContents

i = 1
For Each myCell In Range("B10:B16")
If Not InStr(myCell.Value, "(A1)") > 0 Then
Range("A4")(i, 1).Value = myCell.Value
i = i + 1
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP