How replace a character in a string of characters?
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 |
How replace a character in a string of characters?
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 |
How replace a character in a string of characters?
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 wrote: 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 |
How replace a character in a string of characters?
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 |
How replace a character in a string of characters?
Ahh.. this makes sense... I will try it and see how it works.. Thx VERY
much. ! :) Chet wrote: 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 |
How replace a character in a string of characters?
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 wrote: 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 |
How replace a character in a string of characters?
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 "Chet" wrote in message oups.com... 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 |
All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com