Excel macro help with selecting cells 1

[Askalon]

I am trying to run a find and replace type macro on my spreadsheet. I want to find a cell in a column based on part of the cell contents and change it something else. Example: In a column I have RB 603,RB 512, RB 15, TOE17, HAND 01.

I want to go down the column and replace all of the RB's with the number 603 without specifing each value that could possibly show up (thousands of possiblilties).

Thanks,

Clayton

 

[GlennUK]

What about wildcard searching? Doing Edit/Replace with RB* as the "find" string.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Askalon]

I tried this for my macro:

Sub units()
rng = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
Range("M1").Select
For i = 1 To rng
If ActiveCell.Value = "RB*" Then
ActiveCell.Value = "630"
End If
ActiveCell.Offset(1, 0).Select
Next i

End Sub

Which i am sure there is a better method, but I am no programmer.

Thanks,

Clayton

 

[SkipVought]

How about this...

Sub test()
    Dim r As Range
    Do
        Set r = Cells.Find("RB")
        If Not r Is Nothing Then
            r.Value = "'630"
        Else
            Exit Do
        End If
    Loop
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[Askalon]

that did the trick! Thanks a bunch, now on creating message boxes and user input.