leading 0's problem vba

[davewatson86]

evening all

having a bit of a problem i have some data that is exported as follows

[TABLE="width: 313"]
<tbody>[TR]
[TD]000 012 280 B      WEBBING[/TD]
[/TR]
[TR]
[TD]000 012 499        HOSE[/TD]
[/TR]
[TR]
[TD]000 050 300 A  F7M PAINT PENCIL[/TD]
[/TR]
[TR]
[TD]000 051 446 AP     USB CABLE[/TD]
[/TR]
[TR]
[TD]000 051 446 Q      WIR.HARN.[/TD]
[/TR]
[TR]
[TD]000 051 700 C      BRACKET[/TD]
[/TR]
[TR]
[TD]000 054 635        SENSOR[/TD]
[/TR]
[TR]
[TD]000 071 597 C      BOLT SET - S[/TD]
[/TR]
</tbody>[/TABLE]

here is my code

Dim str1, str2 As String
Dim i, itemcount, itemmax As Long
Dim Rng As Range
Dim iRow


Sub Pull_Part_Number()


i = 1
itemcount = 0
itemmax = 0
    
    Application.ScreenUpdating = False
    
    For iRow = 2 To WS_Mcsi_Data.Range("A2").End(xlDown).Row
    If Not WS_Mcsi_Data.Range("A" & iRow & "").Value = "" Then
        itemmax = itemmax + 1
    End If
    Next iRow
    
    Progress_Bar.Show (vbModeless)
    Progress_Bar.ProgressBar1.Max = itemmax
    Progress_Bar.Progresstext.Caption = "Completed " & itemcount & " of " & itemmax & " items. "
    Progress_Bar.ProgressBar1.Value = itemcount
    'Progress_Bar.Repaint
    
'remove EA from the QTY column
    WS_Mcsi_Data.Columns("B:B").Replace What:="EA", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    


    While Range("A1").Offset(i, 0) <> ""      ' loop while desired cell anything other than nothing
        Set Rng = Range("A1").Offset(i, 0)    ' Set Rng to the desired cell
        str1 = Rng                            ' Set str1 to Rng value
        str2 = Left(str1, 18)                 ' Leave first 18
        str2 = Replace(str2, " ", "")         ' Remove any spaces
        Rng.Value = str2                      ' Change the Rng cell to str2 value
        i = i + 1
        itemcount = itemcount + 1
        Progress_Bar.Progresstext.Caption = "Completed " & itemcount & " of " & itemmax & " items. "
        Progress_Bar.ProgressBar1.Value = itemcount
        Progress_Bar.Repaint
    
    Wend


    Application.ScreenUpdating = True
    
End Sub

my problem is that if the part number dose not have a letter on the end it ends up like this

[TABLE="width: 313"]
<tbody>[TR]
[TD]000012280B[/TD]
[/TR]
[TR]
[TD="align: right"]12499[/TD]
[/TR]
[TR]
[TD]000050300AF7M[/TD]
[/TR]
[TR]
[TD]000051446AP[/TD]
[/TR]
[TR]
[TD]000051446Q[/TD]
[/TR]
[TR]
[TD]000051700C[/TD]
[/TR]
[TR]
[TD="align: right"]54635[/TD]
[/TR]
[TR]
[TD]000071597C[/TD]
[/TR]
[TR]
[TD]000071597D[/TD]
[/TR]
</tbody>[/TABLE]

i need to keep the correct amount of 0's in front of the part numbers.

any help would be grateful as im stumped.

cheers
Dave

 

[Fluff]

Try

        Rng.Value = "'" & str2                    ' Change the Rng cell to str2 value

 

[davewatson86]

Try

        Rng.Value = "'" & str2                    ' Change the Rng cell to str2 value

Thanks for the reply fluff ill have to try it when I'm at work in the morning.

Just a thought though would the ’ interfere if I used a match formula for the part number?

 

[Fluff]

No, it shouldn't be a problem