score:3
Accepted answer
Make sure the cell is formatted as text before storing value to it.
.Cells(LR + 1, index).NumberFormat = "@"
.Cells(LR + 1, index).value = "00049875142"
It will be more efficient though to not set format for each cell at a time, but do for the range before looping through and setting the values.
score:2
You can either add leading zeroes with a custom number format (which doesn't seem to be standard across multiple cells) or input the data as text.
.Cells(LR + 1, index).value = "'00049875142"
'more loops
.Cells(LR + 1, index).value2 = "'0008789875949898451142"
The second needs to be input as text in any event since it is larger than 15 digits and the 15 digit precision rule will truncate off important data.
.Cells(LR + 1, index).numberformat ="00000000000"
.Cells(LR + 1, index).value = 49875142
'more loops
.Cells(LR + 1, index).numberformat ="@"
.Cells(LR + 1, index).value2 = "0008789875949898451142"
Source: stackoverflow.com
Related Query
- Storing data and not outputting with Intended Format
- Excel Macro - Select all cells with data and format as table
- Copy a range of cells and only select cells with data and just the value not the formulas
- Exporting Excel data as CSV with VBA does not export correct file type and cell values
- Vba Worksheet_Change event does not trigger when copy and paste data into column but works with a manual click into cell
- How to solve the "program not responding error" when i extract data with same name in a column and put them into different worksheets?
- EXCEL VBA get raw data format with DATE() and get the date a week ago
- VBA or to not VBA for compare and count cell data with unique ids
- Trying to fetch data from webpage with a VBA code, but sometimes it works and sometimes it does not fetch
- Import data and text format from word table to excel with excel-vba
- Consolidate data for weeks to months and quarters, weeks are like W1, W2 and not in date format
- Create Chart from Array data and not range
- VBA Excel - Insert row below with same format including borders and frames
- I want data of 50 random array with 5 column and 10 row, with unique and no duplicate value. Here why duplicate values are showing?
- Error importing and exporting excel data to vb.net, "Microsoft.Office.Interop.Excel.Application is not defined"
- Convert String to Excel Date and Time, with specific string format
- Copy a range of cells and only select cells with data
- How can I iterate over an array and populate some cells with 1 if the array contains the value, and 0 if not ,in VBA or Excel?
- Why does "undo" not work with an Outlook macro that marks as read and moves mail items?
- VBA Double as String with comma and not point
- How to retrieve data from Excel with ADODB connection if the first line of the worksheet does not have the column name?
- Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text
- Open CSV file with correct data format for each column using TextFileColumnDataTypes?
- Error in VBA:`[Microsoft][ODBC Manager] Data source name not found and no default driver specified
- VBA macro in Excel 2016 for Mac: SaveAs will not work with a CSV file format
- How to populate data from a range (multiple rows and columns) to listbox with VBA
- VBA for...next loop with byte data type not supported?
- Microsoft Excel Copy and Paste with Format of Date
- Can I form arrays for only cells with calculated data, and disregard cells with no data and just underlying formulas?
- Regex with hyperlinks ActiveDocument.Range and Format
More Query from same tag
- Need VBA to recognize date and not just numbers
- VBA code to Highlight duplicate columns in excel
- Deleting Rows via For-Loop (within a multi Area Range)
- ROUNDDOWN in Excel
- VBA Can I set name using integer?
- Excel macro VBA code to calculate average of a string of numbers
- VBA - Excel Organizing/Duplicating Data
- Select records where more than one field has data in the same record - a customer can order one or more books
- How to login into a website without using sendkeys?
- Build one list out of two with Excel VBA
- How to extract text after second space in VBA
- is there a way to truncate multiple functions into one?
- Run time error 91 in VBA macro to hit search button in website Excel VBA
- How to use same macro to read all worksheets
- What to do with VBA Query Result
- TypeOf object not detecting label and textbox in excel vba userforms
- Dynamic filter keeps hiding my rows - Excel VBA
- Inserting date into string
- Installable ISAM Error when trying to use DoCmd.TransferDatabase to link a table
- Sum innertext VBA IE
- VBA convert all cells in column to type text
- Array: Compile Error. Wrong number of variables
- VBA Access Specify Field Format for a Date/Time Field
- Checking if File is open to prevent error
- 424 Run-time error
- Export Access data into Excel workbook and split data into multiple sheets based on column value
- GetObject(, "Outlook.Application") not working with Outlook open
- MS Access Appln connecting to SQL Server2005 DSN-less
- An issue when generating text with national symbols using VBA in Excel 2019
- excel vba open files and copy documents based on monotonically increasing name
