VBA: Cell values to numeric?

JarmoVee

New Member
Joined
May 27, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Now sending values from cells they goes in text-format (to cells F and G). Need to be numeric values, because these values have to import to the other system.
How to do this?


VBA Code:
    Set wbGood = ThisWorkbook
    Set wbGood = Workbooks("Import.xlsx")

    Set wsCopy = ThisWorkbook.Worksheets("Import")
    Set wsDest = Workbooks("Import.xlsx").Worksheets("Sheet1")

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "F").End(xlUp).Offset(1).Row

    wsCopy.Range("A2:M" & lCopyLastRow).Copy
    wsDest.Range("A" & lDestLastRow).Resize(lCopyLastRow - 1, 13).Value = wsCopy.Range("A2:M" & lCopyLastRow).Value

    Application.CutCopyMode = False
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
Can you show me using a picture, of where you are seeing that F&G are producing text.
Also for the same rows what it looks like on the original sheet.
When I run your assignment statement (2nd last line of your code), it converts text that looks like a number to a number not the other way around.
Is there any chance you have formatted columns F&G in the Destination sheet as Text & can you check what formatting appears in those columns when you see the values appear as text there ?
Is "the other system" reading the output sheet or is that sheet being saved as csv for the other system to import ?
 

JarmoVee

New Member
Joined
May 27, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Can you show me using a picture, of where you are seeing that F&G are producing text.
Also for the same rows what it looks like on the original sheet.
When I run your assignment statement (2nd last line of your code), it converts text that looks like a number to a number not the other way around.
Is there any chance you have formatted columns F&G in the Destination sheet as Text & can you check what formatting appears in those columns when you see the values appear as text there ?
Is "the other system" reading the output sheet or is that sheet being saved as csv for the other system to import ?

First of all, apologies for my poor English. The data goes from source sheet to “import.xlsx” and I checked the format, which all columns have a value format. Also in the source sheet columns are with value format. My intention is to transfer the product title/description and prices from the “import.xlsx” file to the invoicing program. So the invoicing program now reads the prices as text format and does not transfer the decimals.
 

Attachments

  • txtFormat.JPG
    txtFormat.JPG
    26.8 KB · Views: 4
  • ValueFormat.JPG
    ValueFormat.JPG
    42.4 KB · Views: 4

JarmoVee

New Member
Joined
May 27, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
And forgot....
  • Save and importing file at Excel as XLSX-format to Invoicing program
 

JarmoVee

New Member
Joined
May 27, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I think I have solved this problem. Must be used with user form when filling in a dot instead of a comma (e.g 10,55 -> 10.55)
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
Great, let me know if that's not the case. Thanks for keeping me updated.
 

Forum statistics

Threads
1,141,062
Messages
5,704,044
Members
421,325
Latest member
tapete86

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
Top