Need help in finishing a macro..

[jocote46]

I've been trying to create a macro with AI help but I couldn't get a code to work. I'm going to post the code that i have and it does some of the thing that i want, like adding columns. i will submit a file (fictitious data) but it will give you an idea of what i need to accomplish. this is what i have and it works so far, except for the formulas (haven't tested those yet).

Sub UpdateSheet()
    Dim ws As Worksheet
    Set ws = ActiveSheet
 
 
    ActiveSheet.Select
    Range("K1").Value = "BU"
    Range("M1").Value = "GL"
    Range("N1").Value = "-"
    Range("O1").Value = "00"

    ' Set color
    ws.Range("G1,I1,K1,M1").Interior.Color = RGB(165, 214, 167)
     
 
    Range("K2").Formula = "=VLOOKUP(H2,reference!$A$1:$B$21,2,0)"
    Range("M2").Formula = "CONCATENATE(G2,N2,H2,N2,O2,N2,I2,N2,""60055-000"")"

the part that i need to help with is the following:
1. I have data in Columns "G", "H" and "I". stored as text. i want to add a leading zero in Column G to any existing number (2,3,9) and get the value to be shown as '02,'03 and '09.
2. In Column H, i have 3 digits numbers , also as text and i want to add a leading zero so that all numbers have four numbers total, i have '299. '497.'499 etc. s/b '0299,'0497 etc
3. In column I, i have a 2 digit number , as text. i want number 99 to be converted to '00.
4. i have a total of about 215 rows but number could varied.
Please let me know if you have any questions and thank you in advance for all your help. you are the best!

 

[Max1616]

Give this a try:

Dim lrow As Long
Dim i As Long
Dim j As Long


'Dynamically calculate the number of rows (assumes there's no additional data in column A below your data set)
lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lrow 'Loop through each row
    'Columnn G, add leading 0 in front of 2, 3, and 9
    If Cells(i, 7) = "2" Or Cells(i, 7) = "3" Or Cells(i, 7) = "9" Then
        Cells(i, 7) = Format("0" & Cells(i, 7), "@")
    End If

    'In column H add leading zeros to make each value = 4 digits
    If Len(Cells(i, 8)) < 4 Then
        For j = 1 To 4 - Len(Cells(i, 8))
            Cells(i, 8) = Format("0" & Cells(i, 8), "@")
        Next j
    End If
    
    'In column I convert 99 to '00
    If Cells(i, 9) = "99" Then
        Cells(i, 9) = Format("'00", "@")
    End If
    

Next i

 

[jocote46]

Give this a try:

Dim lrow As Long
Dim i As Long
Dim j As Long


'Dynamically calculate the number of rows (assumes there's no additional data in column A below your data set)
lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lrow 'Loop through each row
    'Columnn G, add leading 0 in front of 2, 3, and 9
    If Cells(i, 7) = "2" Or Cells(i, 7) = "3" Or Cells(i, 7) = "9" Then
        Cells(i, 7) = Format("0" & Cells(i, 7), "@")
    End If

    'In column H add leading zeros to make each value = 4 digits
    If Len(Cells(i, 8)) < 4 Then
        For j = 1 To 4 - Len(Cells(i, 8))
            Cells(i, 8) = Format("0" & Cells(i, 8), "@")
        Next j
    End If
   
    'In column I convert 99 to '00
    If Cells(i, 9) = "99" Then
        Cells(i, 9) = Format("'00", "@")
    End If
   

Next i

Thanks Max for the code, everything worked fine except for Column H. the number is still showing as 3 digits, without the leading zero. thanks for awesome coding.

 

[jocote46]

Hi Max, for some reason the code on column H was converting the number as text (original formatting) to "general" excel formatting hence the leading zero was not being added. i edited your code to convert the number back to a "text" format and it worked. thank so much for helping on this, I truly appreciate it. keep up the awesome work.

If Len(Cells(i, 8).Value) < 4 Then
For j = 1 To 4 - Len(Cells(i, 8).Value)
Cells(i, 8).NumberFormat = "@" ' Set the cell format to text
Cells(i, 8).Value = "0" & Cells(i, 8).Value
Next j
End If

 

[jocote46]

Sub UpdateSheet()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lrow As Long
Dim i As Long
Dim j As Long


ActiveSheet.Select
Columns("J:J").Insert
Range("J1").Value = "BU"
Range("M1").Value = "GL"
Range("N1").Value = "-"
Range("O1").Value = "'00"

' Set color
ws.Range("G1,I1,J1,M1").Interior.Color = RGB(165, 214, 167)


'Dynamically calculate the number of rows (assumes there's no additional data in column A below your data set)
lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lrow 'Loop through each row
'Columnn G, add leading 0 in front of 2, 3, and 9
If Cells(i, 7) = "2" Or Cells(i, 7) = "3" Or Cells(i, 7) = "9" Then
Cells(i, 7) = Format("0" & Cells(i, 7), "@")
End If

'In column H add leading zeros to make each value = 4 digits
'If Len(Cells(i, 8)) < 4 Then
' For j = 1 To 4 - Len(Cells(i, 8))
' Cells(i, 8) = Format("0" & Cells(i, 8), "@")
' Next j

If Len(Cells(i, 8).Value) < 4 Then
For j = 1 To 4 - Len(Cells(i, 8).Value)
Cells(i, 8).NumberFormat = "@" ' Set the cell format to text
Cells(i, 8).Value = "0" & Cells(i, 8).Value
Next j

End If


'In column I convert 99 to '00
If Cells(i, 9) = "99" Then
Cells(i, 9) = Format("'00", "@")
End If

Next i

Range("N2").Value = ("-")
Range("O2").Value = ("'00")
Range("J2").Formula = "=VLOOKUP(H2,reference!$A$1:$B$21,2,0)"
Range("M2").Formula = "=CONCATENATE(G2,N2,H2,N2,O2,N2,I2,N2,""60055-000"")"

With ActiveSheet

Range("N2").AutoFill .Range("N2:N" & .Cells(.Rows.Count, "D").End(xlUp).Row)

'Find the last row in column N
lastRow = Cells(Rows.Count, "N").End(xlUp).Row

'Copy the header to all cells from O2 to the last row in column O
Range("O2:O" & lastRow).Value = "'" & Range("O1").Value

Range("J2").AutoFill .Range("J2:J" & .Cells(.Rows.Count, "D").End(xlUp).Row)
Range("M2").AutoFill .Range("M2:M" & .Cells(.Rows.Count, "D").End(xlUp).Row)

End With

ActiveSheet.UsedRange.EntireColumn.AutoFit

End Sub

So here is the final code for my project. thank you again Max for your great help. it might be too long but at the moment i'm still learning and i build it piece by piece.