Leading Zero issue

S

Sushiboy

New Member
Joined
Jan 18, 2019
Messages
24
I have an issue with the formatting of data.

the script I have copies data from "PASTEDAT" tab and paste into "QUASAR Script" tab.

So the data from the "Data"tab can be as follows

546882
546883
'092384

Copying the 546XXX numbers are ok. However, the resultant for the last number pastes into the cell as 9234

How can I resolve this? The script I have is below

Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000]SubLifeTimeMortgagesMacro()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sheets("PASTEDATA").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]WithSheets("PASTEDATA")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]lastrow = .Range("A" &Rows.Count).End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]For i = 3 To lastrow[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]If IsError(Cells(i, 5)) Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]If Cells(i, 5) <> 0 Then[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sedol = Cells(i, 3).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Value = Round(Cells(i, 5), 7)[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sheets("QUASARSCRIPT").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]IfIsEmpty(Range("A65536").End(xlUp)) Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("A65536").End(xlUp).Activate[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("A65536").End(xlUp).Offset(1, 0).Activate[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(0,0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Sedol[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color =6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]ActiveCell.Offset(1,0).Value = "Y"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]ActiveCell.Offset(2,0).Value = "Y"[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(3, 0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color =6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(4,0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color = 6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(5,0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color =6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sheets("PASTEDATA").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Sushiboy said:
I have an issue with the formatting of data.

the script I have copies data from "PASTEDAT" tab and paste into "QUASAR Script" tab.

So the data from the "Data"tab can be as follows

546882
546883
'092384

Copying the 546XXX numbers are ok. However, the resultant for the last number pastes into the cell as 9234

How can I resolve this? The script I have is below

Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000]SubLifeTimeMortgagesMacro()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sheets("PASTEDATA").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]WithSheets("PASTEDATA")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]lastrow = .Range("A" &Rows.Count).End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]For i = 3 To lastrow[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]If IsError(Cells(i, 5)) Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]If Cells(i, 5) <> 0 Then[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sedol = Cells(i, 3).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Value = Round(Cells(i, 5), 7)[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sheets("QUASARSCRIPT").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]IfIsEmpty(Range("A65536").End(xlUp)) Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("A65536").End(xlUp).Activate[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("A65536").End(xlUp).Offset(1, 0).Activate[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[COLOR=#ff0000][B][FONT=Times New Roman][SIZE=3]With ActiveCell.Offset(0,0)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3].Value = Sedol[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3].Interior.Color =6737151[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End With[/SIZE][/FONT]
[/B][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]ActiveCell.Offset(1,0).Value = "Y"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]ActiveCell.Offset(2,0).Value = "Y"[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(3, 0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color =6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(4,0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color = 6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]With ActiveCell.Offset(5,0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Value = Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000].Interior.Color =6737151[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sheets("PASTEDATA").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
Click to expand...
Untested but, if your data are always 6-digit numbers, try replacing the With-EndWith block highlighted in red(above) with this:

Rich (BB code): 
With ActiveCell.Offset(0, 0)
    .NumberFormat = "000000"
    .Value = Replace(Sedol, "'", "")
    .Interior.Color = 6737151
End With
 
Upvote 0
That seems to have worked Joe.

The data will always be a variation of 5 or 6 or 7 digits (The digits are a SEDOLs (Stock Exchange Daily Official List)

Its only an issue when a prefix of zero is required as per above example.

Many thanks for your assistance.

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Tab Selection from Userform based on Date range
Replies
5
Views
116
campbell1093
C
S
  • Solved
Search for entered value in another tab
Replies
3
Views
113
skittlz
S
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
487
Mplz
M
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
310
kvsrinivasamurthy
K
Rhodie72
  • Question Question
How to write a data to a text file (VBA)
2
Replies
17
Views
965
Rhodie72
Rhodie72

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top