[Macro help request] formatting the integers.

[misiek]

Hi all,

My first post here, I am a bit nervous.

First off, I consider myself as belonging to the pool of 99% Excel users who use 1% of its power.

Came across this forum the other day and it looked like the right place to ask questions.

Let me begin with the data structure:

ColumnA | ColumnB
AAA | 12345678
BBB | 90123456

I am trying to format the integer numbers in ColumnB in the following way:
If ColumnA reads AAA, add TRAILING zeros to the number in ColumnB of the same row until there are 14 digits there.
If ColumnA reads BBB, add LEADING zeros to the number in ColumnB until there are 10 digits there.

Could you guys/gals give me some examples on how this could be done?
Since there are potentially hundreds of rows that need to be formatted this way, doing this manually is a nightmare.

Thanks in advance

 

[misiek]

Hi

Hummm, now I understand what you are after.
You didn't need col.A letter to lead.

Sub a() 
Dim r As Range 
For Each r In Range("a1", Range("a65536").End(xlUp)) 
    If Not IsEmpty(r) Then 
        If r.Value = "AAA" Then 
            r.Offset(, 2) = r.Offset(, 1) & _ 
                WorksheetFunction.Rept(0, 14 - Len(r.Offset(, 1))) 
        Else 
            r.Offset(, 2) = Format(r.Offset(, 1), "0000000000") 
        End If 
    End If 
Next 
End Sub

should work

rgds,
jindon

Almost there, this is the output:

AAA,12,12000000000000
BBB,34,34 (desired format is 0000000034)
AAA,56,56000000000000
BBB,78,78 (desired format is 0000000078)
AAA,90,90000000000000

Thanks

Sorry for being a pain-in-the-neck.