Setup Cells Without Looping

[Gary''s Student]

If I enter data in A1 thru C1 and run:

Sub sample1()
Set r1 = Range("A1:C1")
Set r2 = Range("A3:C3")
r2.Value = r1.Value
End Sub

The values get correctly setup in A3 thru C3, but if I run:

Sub sample2()
Set r1 = Range("A1:C1")
Set r2 = Range("A3:C3")
r2.NumberFormat = r1.NumberFormat
End Sub

the formats do not get setup. Why?

 

[Stringer]

I copied your code and set
A1:C1 as Date
A3:C3 as text
Ran the code
A3:C3 became Formatted as dat

 

[JLGWhiz]

Maybe I didn't make enough tests, but the one I ran worked. I set A1:C1
number format to 0.00 then ran the macro and it changed the numbers in A3:C3
to 0.00 format.

 

[JLGWhiz]

I ran several more tests and could not make it hiccup. The code seems to
work. Must be another problem.

 

[Mike H]

it doesn't work with mixed formats, and I think that's the point GS is making

 

[Gary''s Student]

Strange I still can't get it to work.

I formatted A1 with one decimal place
I formatted B1 with two decimal places
I formatted C1 with three decimal places

after I run sample1 I get:

1.0 1.00 1.000

1 1 1

after I run sample2 nothing changes !?!

 

[Gary''s Student]

Strange I still can't get it to work.

I formatted A1 with one decimal place
I formatted B1 with two decimal places
I formatted C1 with three decimal places

after I run sample1 I get:

1.0 1.00 1.000

1 1 1

after I run sample2 nothing changes !?!

 

[Gary''s Student]

Is the Value property somehow "special"?
The only way I get the formats to set up is if I loop cell-by-cell.
What am I doing wrong?

 

[Mike H]

GS,

Like yourself I could do it other ways but I'm still trying to understand
why this doesn't work. I was working on the same one as yourself 1,2 &3
decimal places to try and understand what's going on

Mike

 

[Dave Peterson]

Not all properties can be retrieved this way (as you've seen).

Why? Because MS said so. <bg>

 

[FSt1]

hi
i did some test too. it works if all the formats are the same but doesn't if
the formats are different.
using xl02 on xp here.
seems the xl code allows for the transfer of different values range to range
but not different formats. odd.
i'm at a loss. but i will just make note of this as something learned.

Regards
FSt1

 

[JLGWhiz]

This worked OK, I guess Dave is right, Microsoft didn't want it to work the
other way.

Sub sample2()
Set r1 = Range("A1:C1")
Set r2 = Range("A3:C3")
r1.Copy
r2.PasteSpecial Paste:=xlPasteFormats
End Sub

 

[Jon Peltier]

When dealing with the contents of cells, Excel/VBA can extract values, text,
or formulas in an array. Any other property cannot be extracted as an array,
but merely as a scalar value. To get this scalar, VBA uses the property
value for the top left cell.

Why not use Copy > Paste Special > Values?

- Jon

 

[Jon Peltier]

DOH!! I meant Copy > Paste Special > Formats!

- Jon

 

[FSt1]

hi
Copy > Paste Special > Formats! may be the way. as stated, i'll write it up
to something learned today. thanks for the explination.

Regards
FSt1

 

[Gary''s Student]

Thank you Jon.

I guess it is the way it is.

Thank you all for taking the time to help me!

 

[Gary''s Student]

Thank you Dave. I am slowly building an understanding of what
properites/methods need a loop and which do not.

 

[Dave Peterson]

The only way I know them is to try them (well, except for .value).

Thank you Dave. I am slowly building an understanding of what
properites/methods need a loop and which do not.

 

[Jon Peltier]

..Value, .Text, and .Formula, but I've had the unfortunate experience to
discover that writing an array to a range as .Formula does not work as
expected in some language versions of Excel 2003 (the problem may be more
widespread). I tested on my machine, and it worked fine. The client reported
problems. I could see that his sheet had the wrong formulas. I tried with
his sheet and it worked. Then I had him do it while I watched over
GoToMeeting, so I could validate the array contents (which were correct),
watch the assignment of .Formulas, and immediately check the formulas, which
were wrong: all cells assumed the formula intended for the top left cell. So
I had to resort to a loop.

- Jon

 

[Gary''s Student]

PasteSpecialFormats also pastes the font-type, the font color, the background
color, the alignment, the borders, etc. All I want is the NuberFormat copied.

I will use cell-by-cell.