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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
Colouring cells based on contains value
Replies
2
Views
140
CawstonT
C
S
  • Question
VBA Duplicates
Replies
1
Views
201
Alex Blakenburg
A
E
  • Solved
VBA -highlighting rows when a condition is met
Replies
2
Views
170
Evie76
E
W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
111
Micron
M
E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
226
Joe4
Joe4

Forum statistics

Threads
1,214,944
Messages
6,122,387
Members
449,080
Latest member
Armadillos

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