need VBA to find and replace part of a value in a cell

[Marijke]

Hi,

1 of our inventory systems is creating a daily output into excel, but we have discovered a problem on 1 of the columns....
The serial number column in Excel does not like it if the value is starting with 2 zero's. If it's a numeric number only the Excel automatically drops the 2 zero's in front of the number.
We have already tried adjusting the system, but unfortunatly this will not work.
So, we are down to VBA to automatically update these values.

I need a way to pull the 2 zero's back in, so i was thinking of some type of 'find/replace' formula in VBA, but I only now how to do this for complete words (basically a 1 of 1 match).
I have a feeling this is going to be a very easy fix.... ;-)

I need to formula to read,
if the value in column Z begins with 27 , then replace that value with '0027

Thanks.

[Break_Point]

I would just use a formula instead of vba.

If the serial number 27 is in cell B1 then in cell C1 type ="'00"&B1
Then drag the formula down.

This will precede your serial numbers with '00. This assumes all your serial numbers require the '00 to be added to them, if not then vba may be a better alternative.

HTH

[Marijke]

sorry, should have added this on my post...
we're already using VBA to make the report look nice for our customer, so would be easiest if we could add it and do this all in 1 go if you see what i mean.

[Marijke]

btw, our current VBA is built up by the following ´assignments types´

Range("BB8").Formula = "=counta(W1:W65000)"
x = Range("BB8").Value + 1


Range("A3").Select
For i = 3 To x
If Range("A" & i).Value = "" Then
ActiveCell.FormulaR1C1 = _
"=VLOOKUP......................."
End If
ActiveCell.Offset(1, 0).Select
Next i


Not sure if this could be helpful when creating a formula to add the 2 zero´s in the same style?

[Break_Point]

This should work, assuming:
1. All serial numbers need to be checked and changed,
2. The below assumes serial numbers are in column A and
3. The code works for row 1 to the number of rows used in the sheet.
(You will need to change the column and row variables as per your report)

Please Login or Register  to view this content.

HTH

[Marijke]

I've tested it HTH, and we are moving into the right direction. However, instead of adding '00 in front of the serial number field, it replaces it completely.
May be better if i give you an example.

The following serial number is currently appearing on the reports: 2704202206
It actually needs to read: 002704202206

with the Sub you provided, the number 2704202206 is replaced by '00

as far as your questions/remarks are concerned:
1. All serial numbers need to be checked and changed, --> it's OK to check them, but only change the ones that meet the criteria
It's only applicable for the serial numbers that begin with 27. There could be serial number that contain 27 in the middle of the serial number, but the macro needs to ignore those records.

2. The below assumes serial numbers are in column A and --> that's fine, I can change this if needed.

3. The code works for row 1 to the number of rows used in the sheet.
(You will need to change the column and row variables as per your report) --> Done



we also have some non-serialized equipment on our reports. The way the sub is setup currently, makes it pull '00 into the serial number field also.
can you set it to ignore the blank fields?

[Break_Point]

Hey,

This checks the serial number column (in my example as column A) and changes any serial begining with 27 to begin with '0027.

Please Login or Register  to view this content.

[Marijke]

this worked perfectly! Thanks.