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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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

JohnZ1156
  • Question
Check if date exists in entire Date column of Table4
Replies
1
Views
54
Tetra201
Tetra201
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
171
Evie76
E
W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
111
Micron
M

Forum statistics

Threads
1,214,957
Messages
6,122,472
Members
449,087
Latest member
RExcelSearch

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