VBA to Find and Replace Only in 1 Column

[gunny2k9]

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

[vba]
Sub FindReplace()

Dim a As Long

For Each a In ActiveSheet.UsedRange

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

Next

End Sub
[/vba]

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

[vba]
Sub FindReplace()

Dim a As Long

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

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

Next

End Sub
[/vba]

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]

[vba]

Activesheet.Columns("H").Replace _
What:="find", Replacement:="replace"
[/vba]

[gunny2k9]

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]

And does this not fall over?

[vba]

Sub FindReplace()

Dim a As Long

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

a = Replace(a, "Find", "Replace")
Next
End Sub
[/vba]

[gunny2k9]

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]

See if this is better

[vba]

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
[/vba]