CODEWITHSUNDEEP
excelvba
Thomas
Thomas

Reputation: 113

Replace semi-colon with comma

I want to replace a semi-colon with a comma in a range.

The following code only deletes the semi-colon, it doesn't put the comma in its place.

  Range("AU2:AU250").Select
  Selection.Replace What:=";", Replacement:=",", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

Upvotes: 0

Views: 3405

Answers (2)

Rodger
Rodger

Reputation: 841

Can you copy paste some of the offending cells from the CSV range in the csv file? Open it in wordpad though, not in excel.

Also, what version of excel are you using? I haven't managed to get this to occur either on 2003 or 2010.

And I assume you need to do this more than once is why you are making a macro or is find/replace failing and you are posting macro code to get help with that too? :-)

Upvotes: 0

MikeD
MikeD

Reputation: 8941

Try this ...

Sub Comma()
    Dim R As Range, C As Range

    Set R = Range([AU2], [AU250])

    For Each C In R.Cells
        C = Replace(C, ";", ",")
    Next C

End Sub

This is IMHO a more VBA like approach making use of range objects rather than simulating Excel stuff that you would use when interacting directly with your sheet

Upvotes: 1

Related Questions