VBA: moving an array but keeping the format unchanged

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
I have a list of suppliers codes, most of them are in text format like

ABC, DEF, XYZ

however I have a few that are just numbers,

12456
6789
00056

This last one is causing me problems as the leading zeros are lost

I am moving the data, trying to avoid a copy paste:

Code:
Sheets("List").Range("A3:K1000").Value2 = Sheets("Query").Range("A3:K1000").Value2

what could be the best way of keeping the leading zeros?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,012
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
Sheets("List").Range("A3:K1000").Value2 = Sheets("Query").Range("A3:K1000").[COLOR=#0000ff]Text[/COLOR]
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
I tried a few variations of .value .value2 & .text

.text does not move any data across
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
a bit of further testing

Code:
Sheets("List").Range("A4").Value2 = Sheets("Query").Range("A4").Text

works as expected, but

Code:
Sheets("List").Range("A4:K4").Value2 = Sheets("Query").Range("A4:K4").Text

doesn't move any data
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245

ADVERTISEMENT

is anyone able to offer any further help on this one?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can't use Text on a multi-cell range (unless they all happen to contain the same displayed value).

Assuming the source cells actually contain the leading 0s rather than just being formatted to show them, you could set the target range number format to text:

Code:
With Sheets("List").Range("A3:K1000")
.Numberformat = "@"
.Value2 = Sheets("Query").Range("A3:K1000").Value2
end with
 

Watch MrExcel Video

Forum statistics

Threads
1,122,707
Messages
5,597,682
Members
414,164
Latest member
ARTW

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