Need help with a VBA Macro - replacing value

bbznyc

Registered User.
Local time
Yesterday, 19:46
Joined
Aug 9, 2004
Messages
22
Hi,

It must be a piece of cake but I can't figure out how to do this:

Current Code:
Range(("A2"), Range("B65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart

Current Problem:
I have numbers in a cell with leading zero's and spaces at the end. I need to remove the blanks at the end but keep the zeros in the front. When I remove the spaces, zero's go away. Formating to text doesn't work as zeros still disappear.

My solution:
I need the code to find the first value of a cell and if its 0, replace with ' and 0...

Please help... :)

Thanks...
 
Well you'll have to check each cell otherwise you won't be able to detect a cell starting with 0.

try :-
Code:
dim lnX as long, rCell as range
lnx = row(Range("B" & activesheet.rows.count).end(xlup)
for each rcell in range("A2:B" & lnX)
 if left(rcell.value,1) = "0" then rcell.value = "'" & rcell.value
 if left(rcell.value,2) = " 0" then rcell.value = "'" & rcell.value ' to be sure
 rcell.value = replace(rcell.value," ","")
next rcell
 
Just to satisfy my curiosity and help over come my ignorance, how does one get leading 0 in a cell without having ' at the start?

Thanks
Brian
 
acutally the way i get the data is as follows:
there can be multiple spaces in the quotes...

="392094 "
 
The above given solution will replace all " " (spaces) with "" (nothing) so it should cope with your data.
 

Users who are viewing this thread

Back
Top Bottom