Need help with an vba macro for excel that adding an 0 in a number combination

[pfr]

Hi, I'm new to VBA so I need a little help with a macro.

I have an excel file with around 70 sheet (can be more or less sheets every day) and I need a macro that checking a number combination in column "G", the combination variates between 12 and 13 numbers.

What I need is an macro that simply adding an 0 as the first number if the number combination have 12 numbers and leave the numbers as they are if they already contain 13 numbers.
The data variates all from 5 rows to over 10000 rows in the sheets, so I need the macro to loop throw the entire column "G" in each sheet to the last sheet.

For example:
123412341234(should be changed to 0123412341234)
and
0123412341234 (shall the macro leave as it is because it's already containing 13 numbers)

There are a header from row 1 to 4 and the data always starts at row 5, so the macro shall start to looking for 12 numbers combinations and always add an 0 at the start of the number combination.
The macros shall only check and do the change in column G5 and down to the last product in each sheet.

Hope anyone can help me with this.

[p45cal]

If the cells in column G are formatted 'General' then trying to add a leading zero will end up with Excel removing it again.
I'm guessing that these numbers are not numbers which you need to do maths on, and are like an account number, or some such.
How does the data get into those cells. Does the data originally have a leading zero? If the cells are formatted as Text, before the data is put there, they might keep their leading zero, and it will save you a lot of work.
Otherwise this little macro, when run, acts on the currently selected cells:
Sub blah()
Selection.NumberFormat = "@"
For Each cll In Selection
cll.Value = Format(cll.Value, "0000000000000")
Next cll
End Sub

[pfr]

its not working, notthing happends when i run it, maybe a mgs box with how many changes that have been done, would be great

[pfr]

anyone that can help me with this, the macro above dont work and i want an messenger box that let me know how many actions it changed

[Paul_Hossler]

Try this then




Option Explicit

Sub PadCells()
Dim i As Long
Dim r As Range, c As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set r = Intersect(Selection, Selection.Parent.UsedRange)

r.NumberFormat = "@"

For Each c In r.Cells
If Len(c.Value) = 12 Then
c.Value = "0" & c.Value
i = i + 1
End If
Next

MsgBox i & " cells changed"

End Sub

[p45cal]

dont work
Great information.
Did you select cells BEFORE running the macro? I said:
Otherwise this little macro, when run, acts on the currently selected cellsTo add a report on how many cells have been processed:
Sub blah()
Selection.NumberFormat = "@"
For Each cll In Selection
cll.Value = Format(cll.Value, "0000000000000")
Next cll
msgbox Selection.cells.count & " cells processed."
End Sub


Attaching a sheet with such problem data would really help, so that solutions could be tested and time saved.
And of course, answering my other questions might help you not have the problem in the first place. Sigh.

[pfr]

Great information.

Attaching a sheet with such problem data would really help, so that solutions could be tested and time saved.
And of course, answering my other questions might help you not have the problem in the first place. Sigh.

sorry for my misunderstanding here.
Yes its working if i mark the cells, but can't it work in range G5 and down to the last product in each sheet, it should also loop throw the entire file, it will take some time to mark the numbers in all 60-70 sheets in the file and run the macro.

It's also saying that it processed 155 cells (i tried on a sheet with 155 numbers in column G, can I get it to say exactly how many cells it changed and added the 0 on instead of all the products ?

[pfr]

i added a small test file

[p45cal]

The following works on all the sheets in the currently active workbook:
Sub blah()
Dim rngToProcess As Range
Z = 0: y = 0
For Each sht In ActiveWorkbook.Sheets
y = y + 1
Set rngToProcess = Intersect(sht.Columns(7), sht.UsedRange, sht.Rows("5:" & sht.Rows.Count))
If Not rngToProcess Is Nothing Then
rngToProcess.NumberFormat = "@"
For Each cll In rngToProcess
x = Len(cll.Value)
cll.Value = Format(cll.Value, "0000000000000")
If x <> Len(cll.Value) Then Z = Z + 1
Next cll
End If
Next sht
MsgBox Z & " cells changed." & vbLf & y & " sheets examined"
End Sub


It would be more robust if this didn't need to happen at all. Tweaking how the data gets into the worksheet in the first place is safer.

[SamT]

Based on p45cal's excelent code.

As written this code belongs in the Personal.xlsm workbook, so that it can be run on any workbook.

However,for testing purposes, I commented the two lines it needs to work when it's in Personal.xlsm. AS IS, you can test it in any workbook.


Option Explicit

Sub Make_All_13()

Dim Sht As Worksheet
Dim rw As Long
Dim LR As Long
Dim cel As Range

If ThisWorkbook Is ActiveWorkbook Then
'MsgBox "PLease select the workbook to operate on before running this Macro"
'Exit Sub
End If


Application.ScreenUpdating = False
For Each Sht In ActiveWorkbook.Sheets
With Sht
Range("G:G").NumberFormat = "@"
For rw = 5 To .Cells(Rows.Count, "G").End(xlUp).Row
With Cells(rw, "G")
.Value = Format(.Value, "0000000000000")
End With
Next rw
End With
Next
Application.ScreenUpdating = True
End Sub

[p45cal]

If you have 10s of thousands of rows to process then this is more than 100 times faster than my previous offering:
Sub blah2()
Dim rngToProcess As Range
Z = 0: y = 0
For Each sht In ActiveWorkbook.Sheets
y = y + 1
Set rngToProcess = Intersect(sht.Columns(7), sht.UsedRange, sht.Rows("5:" & sht.Rows.Count))
If Not rngToProcess Is Nothing Then
rngToProcess.NumberFormat = "@"
nnn = rngToProcess.Value
For i = 1 To UBound(nnn)
x = Len(nnn(i, 1))
nnn(i, 1) = Format(nnn(i, 1), "0000000000000")
If x <> Len(nnn(i, 1)) Then Z = Z + 1
Next i
rngToProcess.Value = nnn
End If
Next sht
MsgBox Z & " cells changed." & vbLf & y & " sheets examined"
End Sub

[SamT]

Faster?

If Len(nnn(i, 1)) <13 Then
nnn(i, 1) = Format(nnn(i, 1), "0000000000000")
z= z+ 1
End if

[p45cal]

Faster?
Probably. I have shut down my test sheet and code without saving.
100,000 rows of the OP's duplicated data set took less than a second, so I'm not going to test again!