Wrong number format

[bobkap]

I have a text field with entries like this: 001-2024. However, when I bring them over to another worksheet via VBA code I get the entry in scientific notation.
Any help would be greatly appreciated. I've tried reformatting the fields from and to but it still does not fix the problem.

 

[bobkap]

Ok, so maybe post all the code ??
Only because it seems nothing suggested is solving the issue.

Thanks again! NOTE - I'm not using the counter yet until I confirm I have my first record correct.

Sub DINSA_Mo_Report()
'
' DINSA_Mo_Report Macro
'
'Dim BK As Integer
'Dim wks As Worksheet
Dim toprow As Variant, finalrow As Variant, sdatecol As Variant, edatecol As Variant
toprow = 2
Sheets("Report").Activate
sdatecol = Worksheets("Report").Application.match("Start Date", Range("1:1"), 0)
edatecol = Worksheets("Report").Application.match("End Date", Range("1:1"), 0)
'finalrow = Worksheets("Main").Cells(Rows.Count, 1).End(xlUp).Row
sdate = Worksheets("Report").Cells(2, sdatecol)
EDate = Worksheets("Report").Cells(2, edatecol)
Dim Invordcol As Variant, Suppliercol As Variant, Invsdatecol As Variant, Invnumcol As Variant, Invamtcol As Variant, Invshipcol As Variant, typecol As Variant, repordcol As Variant
finalrow = Worksheets("Main").Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Main").Activate


Suppliercol = Worksheets("Main").Application.match("Supplier", Range("1:1"), 0)
Invsdatecol = Worksheets("Main").Application.match("Invoice Date", Range("1:1"), 0)
Invnumcol = Worksheets("Main").Application.match("Invoice #", Range("1:1"), 0)
Invamtcol = Worksheets("Main").Application.match("Inv. Amt.", Range("1:1"), 0)
Invshipcol = Worksheets("Main").Application.match("Shipping", Range("1:1"), 0)
Sheets("Report").Activate
typecol = Worksheets("Report").Application.match("TYPE", Range("1:1"), 0)
repordcol = Worksheets("Report").Application.match("Order #", Range("1:1"), 0)
Invordcol = Worksheets("Report").Application.match("Order #", Range("1:1"), 0)


'For counter = Worksheets("Main").finalrow To 2 Step -1


    'Is invoice date in range of the report we're doing?
    If Worksheets("Main").Cells(finalrow, Invsdatecol) >= sdate And Worksheets("Main").Cells(finalrow, Invsdatecol) <= EDate Then
    'Worksheets("Report").Cells(toprow, Invordcol) = Worksheets("Main").Cells(finalrow, repordcol)
    Worksheets("Report").Cells(toprow, Invordcol) = "'" & Worksheets("Main").Cells(finalrow, repordcol).Text
    
    'next counter
    End If
'Next counter



'
End Sub

 

[Akuini]

I have a text field with entries like this: 001-2024

So, in the source cell (in Worksheets("Main")), it shows 001-2024, right?
What does the formula bar show?
Could you post an image, showing both cell & formula bar?

 

[bobkap]

So, in the source cell (in Worksheets("Main")), it shows 001-2024, right?
What does the formula bar show?
Could you post an image, showing both cell & formula bar?

Actually, it's supposed to show 033-2024. By the way, please excuse my amateurish code writing. Here's that image.

 

[Akuini]

Base on your image, the value is in col F, so try:

 Worksheets("Report").Cells(toprow, Invordcol) = "'" & Worksheets("Main").Cells(finalrow, "F").Text

 

[Michael M]

Also, maybe this before the pastingline

Worksheets("Main").Cells(finalrow, repordcol)numberformat=  "@"

 

[bobkap]

Base on your image, the value is in col F, so try:

 Worksheets("Report").Cells(toprow, Invordcol) = "'" & Worksheets("Main").Cells(finalrow, "F").Text

THAT WORKED!! MEGA THANKS!

 

[bobkap]

Also, maybe this before the pastingline

Worksheets("Main").Cells(finalrow, repordcol)numberformat=  "@"

I seem to have found my solution but thanks very much!!

 

[Michael M]

Good to see you managed to get it sorted.....

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Joe4]

bobkap,
Please note: When marking a post as the solution, please mark the original post that has the solution and not your own post indicating that another post is the solution.
I have updated this for you.