amend value when copied to show full numbers when pasted into new cells

[Patriot2879]

Hi I have the code below where I copy data from Range AL2 to paste into B2 but the numbers look like 2.7Ex12 and I want this to show as 2700444444444 for example how can I amend that in the code? it is the first line below

Private Sub CommandButton2_Click()
Range("AL2", Range("AL2").End(xlDown)).Copy Range("B2")
Range("G2", Range("G2").End(xlDown)).Copy Range("D2")
Range("AH2", Range("AH2").End(xlDown)).Copy Range("AI2")
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:C,2,FALSE)"
    .Value = .Value
End With
With Range("E2:E" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
    .Value = .Value
End With
With Range("AJ2:AJ" & lr)
    .Formula = "=VLOOKUP(LEFT(AI2,LEN(AI2)-2),PC!A:B,2,FALSE)"
    .Value = .Value
End With
End Sub

 

[James006]

Hi,

You could insert the following instruction:

.NumberFormat = "0"

Hope his will help

 

[Patriot2879]

hi James thanks for the update, where would I put this code please? would I put in a new line after range and enter it in?

 

[James006]

Hi,

Is it only the range starting in cell B2 which needs this modification ...?

 

[Patriot2879]

hi yes it will be B2 and down to last entry in B if that makes sense

 

[James006]

Great ... so please test following ...

Private Sub CommandButton2_Click()
Range("AL2", Range("AL2").End(xlDown)).Copy Range("B2")
' Inserted New Format for this range Only '''''''''''''''''
Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"


Range("G2", Range("G2").End(xlDown)).Copy Range("D2")
Range("AH2", Range("AH2").End(xlDown)).Copy Range("AI2")
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:C,2,FALSE)"
    .Value = .Value
End With
With Range("E2:E" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
    .Value = .Value
End With
With Range("AJ2:AJ" & lr)
    .Formula = "=VLOOKUP(LEFT(AI2,LEN(AI2)-2),PC!A:B,2,FALSE)"
    .Value = .Value
End With
End Sub

Hope this will help

 

[Patriot2879]

wow that works great thanks so much

 

[James006]

You are more than welcome ...