Range to Array WITH Numberformat

I was already afraid of that... many thanks anyway, I'll try something else!

Originally Posted by rorya

You'll have to loop through and read either the Text or Numberformat properties for each cell in column A then. You can't get an array out of Range.Text

Sorry for coming back at you, but I still can't find a proper solution.

Here are my ideas:
- Do I really need to scan through each cell for its Numberformat? Or can I somehow save all Numberformats in a single Array with one click?
- Is there perhaps some way I can copy the .Text property of the entire Range to a new sheet? Should I use PasteSpecial to do this?
- Can I somehow put the .Text of the Range into a String Array?
- Why is this rather simple issue so hard to solve? Has nobody ever needed this before? :-)

I just need a fast solution (hopefully without having to go through each cell) since I want this to happen while opening the file (I don't want to keep the user waiting)

Many thanks in advance!

Hahaha okay, no other solutions then :-) :-)
Thank you very much, I'll go through each cell then... ;-)

Good question.

This is a list with product numbers and -codes for my colleagues.
The products in column 1 are numbered 'PL208', 'PL209', 'PL2010' etc., and the associated codes are in column 2.

The product numbers are always ascending, so I wanted to make this as easy as possible for my colleagues
when they need to add a new product number at the bottom: they select the bottom number and drag it down a few rows.

The year is added as a prefix ('20' or '21'), so product '1' becomes '201', or '2011', '2046', '20129' etc.
I used this Numberfomat: "PL20"# and "PL21"#. But now VBA only gets the part AFTER the prefix, i.e. '1', '57', '169'.

I am building a Excel sheet that makes it easier to fill in products in our webshop (through a feed).
When my colleagues type the product number 'PL20155', I want to scan through this list of codes
so it automatically puts the associated code in a cell.
Therefore I need to have the entire cell value (not only '155') since I'll probably have '155' in 2020 and one in 2021 as well.

Quite a story, I hope it makes sense. If not, please let me know. ;-)

I don't do like this for myself, but one way.

Assuming you have data in col.A & Col.B and read the cell format from A2.

Please Login or Register  to view this content.

Wow, thanks, I'll look into that!

You say you don't like it for yourself. What would you prefer instead?

The most reason to use array is to get faster loop, so if I really need to maintain the format, I would get the data directly from the range and, like rorya said, change the format if necessary or apply the cell format after output.

True, that's the reason I was hoping to use an Array for this ;-)

Could you elaborate this a bit more please:

so if I really need to maintain the format, I would get the data directly from the range and, like rorya said, change the format if necessary or apply the cell format after output.

I mean change the data while loop.
e.g
While loop

Please Login or Register  to view this content.

Or after output

Please Login or Register  to view this content.

Wow, I thought I knew quite a lot of VBA :'-D

Some things in your code are new to me, could you please explain these:

Please Login or Register  to view this content.

1. 'With Range' is new to me. Does it mean it applies 'myFormat' and 'myArray' to all cells inside?
2. Why do you use resize?

Please Login or Register  to view this content.

3. What does the '[h1]' mean?
4. Why do you need to resize again?

Thanks for helping me out!

So, you don't understand the nature of array...

1) With ... End With
It is to reference the object.
So, It is equivalent to

Please Login or Register  to view this content.

There are a lot of sites if you search "With End With VBA"

2) What else do you want to use?

3) [h1] means Range("H1")

4) When you deal with the array, Resize property is the MUST to know.
Unlike Copy/Paste, you need to specify the size of range that you want for output the data from array.

There are other natures that you need to know about the array, so you will need to learn.

1) With ... End With
It is to reference the object.

Haha sorry, I knew that. Must have been sleeping ;-)

2) What else do you want to use?

Err, what do you mean? I don't understand why you made the Array larger ;-)

3) [h1] means Range("H1")

Never knew that, thanks! :-)

4) When you deal with the array, Resize property is the MUST to know.
Unlike Copy/Paste, you need to specify the size of range that you want for output the data from array.

Got it, thanks!