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?
--
Gary''s Student - gsnu2007xx

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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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.

"Gary''s Student" wrote:

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?
--
Gary''s Student - gsnu2007xx

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

"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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

"JLGWhiz" wrote:

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

"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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 - gsnu200832


"JLGWhiz" wrote:

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.

"Gary''s Student" wrote:

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?
--
Gary''s Student - gsnu2007xx

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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?
--
Gary''s Student - gsnu200832


"Mike H" wrote:

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

"JLGWhiz" wrote:

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

"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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

"Gary''s Student" wrote:

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?
--
Gary''s Student - gsnu200832


"Mike H" wrote:

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

"JLGWhiz" wrote:

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

"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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

Why? Because MS said so. <bg

Gary''s Student wrote:

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?
--
Gary''s Student - gsnu2007xx

--

Dave Peterson

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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"FSt1" wrote in message
...
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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=117
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=60083

DOH!! I meant Copy Paste Special Formats!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Jon Peltier" wrote in message
...
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, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"FSt1" wrote in message
...
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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=117
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=60083

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

"Jon Peltier" wrote:

DOH!! I meant Copy Paste Special Formats!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Jon Peltier" wrote in message
...
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, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"FSt1" wrote in message
...
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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=117
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=60083

Thank you Jon.

I guess it is the way it is.

Thank you all for taking the time to help me!
--
Gary''s Student - gsnu200832


"FSt1" wrote:

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

"Jon Peltier" wrote:

DOH!! I meant Copy Paste Special Formats!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Jon Peltier" wrote in message
...
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, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"FSt1" wrote in message
...
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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=117
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=60083

Thank you Dave. I am slowly building an understanding of what
properites/methods need a loop and which do not.
--
Gary''s Student - gsnu200832


"Dave Peterson" wrote:

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

Why? Because MS said so. <bg

Gary''s Student wrote:

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?
--
Gary''s Student - gsnu2007xx

--

Dave Peterson

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

Gary''s Student wrote:

Thank you Dave. I am slowly building an understanding of what
properites/methods need a loop and which do not.
--
Gary''s Student - gsnu200832

"Dave Peterson" wrote:

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

Why? Because MS said so. <bg

Gary''s Student wrote:

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?
--
Gary''s Student - gsnu2007xx

--

Dave Peterson


--

Dave Peterson

..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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Dave Peterson" wrote in message
...
The only way I know them is to try them (well, except for .value).

Gary''s Student wrote:

Thank you Dave. I am slowly building an understanding of what
properites/methods need a loop and which do not.
--
Gary''s Student - gsnu200832

"Dave Peterson" wrote:

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

Why? Because MS said so. <bg

Gary''s Student wrote:

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?
--
Gary''s Student - gsnu2007xx

--

Dave Peterson


--

Dave Peterson

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.
--
Gary''s Student - gsnu200832


"Jon Peltier" wrote:

DOH!! I meant Copy Paste Special Formats!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Jon Peltier" wrote in message
...
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, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"FSt1" wrote in message
...
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

"Gary''s Student" wrote:

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 - gsnu200832


"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=117
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=60083