PDA

View Full Version : VBA to Find and Replace Only in 1 Column



gunny2k9
10-07-2010, 02:21 AM
I need a simple Find and Replace VBA code for excel. Dealing with a lot of text in Column H but need to search and replace a few words


Sub FindReplace()

Dim a As Long

For Each a In ActiveSheet.UsedRange

a = Replace(a, "Find", "Replace")

Next

End Sub


Found that code on google simple and works by searching all cells in all columns

but i need it to only search column H

so i tryed


Sub FindReplace()

Dim a As Long

For Each a In ActiveSheet.Range("H:H")

a = Replace(a, "Find", "Replace")

Next

End Sub


but seems to make now an endless loop ?

PLZ any help ?? sorry not very clued up on vba and been trying to sort this for a few days ..... just recording a macro of search and replace all falls over due to the amount of text in the cell (get a Formula to long Error)

the top code works but causes problems by searching all cells in all columns :(

Using Excel 2003

Bob Phillips
10-07-2010, 02:24 AM
Activesheet.Columns("H").Replace _
What:="find", Replacement:="replace"

gunny2k9
10-07-2010, 02:29 AM
Hi thanks for quick reply thats the same code as what recording a macro of Find and Replace which does like the first 3 then falls over as if you do it manualy you gives you a Formula To Long Error due to the amount of text in the cell

Bob Phillips
10-07-2010, 02:34 AM
And does this not fall over?



Sub FindReplace()

Dim a As Long

For Each a In ActiveSheet.Columns("H").Cells

a = Replace(a, "Find", "Replace")
Next
End Sub

gunny2k9
10-07-2010, 02:43 AM
ye sorry just seen in my copying rush i made a boo boo Dim a As Long is ment to be Dim a As Range

i was messing around and forgot to change it back

ur above code seems to work just that the pc i m on is crappy and seems excel is now not responding :P lol if it aint software its hardware problems ;)

Bob Phillips
10-07-2010, 03:29 AM
See if this is better



Sub FindReplace()

Dim a As Range

With ActiveSheet

For Each a In .Range(.Range("H1"), .Range("H1").End(xlDown))

a = Replace(a, "Find", "Replace")
Next
End With
End Sub