How replace a character in a string of characters?

[Chet]

Anyone know how to pick out a digit in a string of characters like
this? =---FS- 12345 I am trying to replace the first character and
my code keeps coming back with "type mismatch error 13". My code is
simple (but wrong). I am trying to get rid of the = character and
replace it with a ' .

Thx
Chet

Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD()

MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row

For Rowy = 2 To MaxRow
Target = Left(Cells(Rowy, 11), 1)
If Target = "=" Then Target = "'"
Left(Cells(Rowy, 11), 1) = Target
Next Rowy

End Sub

 

[cathellisuk]

Hi Chet,
try this revised version

Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD()

MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row

For rowy = 2 To MaxRow
Target = Left(Cells(rowy, 11), 1)
If Target = "=" Then
Cells(rowy, 11) = Mid(Cells(rowy, 11), 2)
End If
Next rowy

End Sub

I think your code was failing because you can only use the LEFT
function for reading a value not for writing.

Cath

 

[Chet]

Hi Cath,
Thanks for your help! I should have mentioned my code is failing at
the Target = Left(Cells(rowy, 11), 1) line. It still is failing at
that point. Any other ideas?

Thx Chet

 

[cathellisuk]

Chet,
AH! it seems your cells that start with = do really contain formulae.
I had assumed there was a quote before the = to make them strings. In
which case try this.

Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD()
Dim Target As String

MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row

For rowy = 2 To MaxRow
Target = Cells(rowy, 11).Formula
If Left(Target, 1) = "=" Then
Cells(rowy, 11) = Mid(Target, 2)
End If
Next rowy


End Sub

Cath

 

[Chet]

Ahh.. this makes sense... I will try it and see how it works.. Thx VERY
much. !
Chet

 

[marcus]

Hi

You could try this. It will remove the = sign and replace with the '
text symbol.

However this results in the formula appearing in the cell ie 'A2+B2 not
the whole number. If this is what you want this should do the trick.

Sub Remove()

Dim StartRng As Range
Set StartRng = Range("a1", Range("A65536").End(xlUp))

For Each cell In StartRng
StartRng.Replace what:="=", Replacement:="'", LookAt:=xlPart
Next

End Sub

 

[NickHK]

Chet,
Not sure if you actually have a formula or text in your cells, but...
The default property of a Range is .Value. So that is what you are working
with here, as you do not specify a property in your code.
There is also the .Formula and .HasFormula which may be what you need
instead, if formulae are involved.

For Rowy = 2 To MaxRow
with Cells(Rowy, 11)
If .HasFormula = True Then .value = "'" & .formula
end with
Next Rowy

NickHK