macro OK in Excel 2003 but NOT in Excel 2000
 

I can run to following code in Excel 2003 but when I run it in Excel 2000 I
get an error/debug at the line Set rngFrom = <<<

Should I declare different variables because of 2000 version?

****
Sub ADP_ExportExcel()
'transpose horizontal column output (multiCol) into single column
(vertical)
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range
With Sheets("sheet1")
Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) <<
End With
Set rngTo = Sheets("sheet2").Range("A1")
For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value '2 columns
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value '3 columns
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value '4 columns
Set rngTo = rngTo.Offset(4, 0) 'number of rows per item
Next rng
End Sub

--
AncientLearner
....never to old to learn something new

macro OK in Excel 2003 but NOT in Excel 2000
 

Give this a try...


LastRowSheet1 = Sheets(1).Cells(Cells.Rows.Count, "A").End(xlUp).Row

Set rngFrom = Sheets(1).Range("A1:A" & LastRowSheet1)





Mark Ivey

"AncientLearner" wrote in message
...
I can run to following code in Excel 2003 but when I run it in Excel 2000
I
get an error/debug at the line Set rngFrom = <<<

Should I declare different variables because of 2000 version?

****
Sub ADP_ExportExcel()
'transpose horizontal column output (multiCol) into single column
(vertical)
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range
With Sheets("sheet1")
Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
<<
End With
Set rngTo = Sheets("sheet2").Range("A1")
For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value '2 columns
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value '3 columns
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value '4 columns
Set rngTo = rngTo.Offset(4, 0) 'number of rows per item
Next rng
End Sub

--
AncientLearner
...never to old to learn something new

macro OK in Excel 2003 but NOT in Excel 2000
 

That line looks ok to me.

What error are you getting?

AncientLearner wrote:

I can run to following code in Excel 2003 but when I run it in Excel 2000 I
get an error/debug at the line Set rngFrom = <<<

Should I declare different variables because of 2000 version?

****
Sub ADP_ExportExcel()
'transpose horizontal column output (multiCol) into single column
(vertical)
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range
With Sheets("sheet1")
Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) <<
End With
Set rngTo = Sheets("sheet2").Range("A1")
For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value '2 columns
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value '3 columns
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value '4 columns
Set rngTo = rngTo.Offset(4, 0) 'number of rows per item
Next rng
End Sub

--
AncientLearner
...never to old to learn something new

--

Dave Peterson

macro OK in Excel 2003 but NOT in Excel 2000
 

I don't see anything wrong in your code that would fail due to version.

However your code would fail if the activesheet is not a worksheet, eg it's
a chart-sheet or no visible sheet. Just in case, it might be worth the two
changes to the commented line below -

With Sheets("sheet1")
' Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
Set rngFrom = .Range(.Range("A1"), .Cells(.Rows.Count, 1).End(xlUp))

Regards,
Peter T


"AncientLearner" wrote in message
...
I can run to following code in Excel 2003 but when I run it in Excel 2000
I
get an error/debug at the line Set rngFrom = <<<

Should I declare different variables because of 2000 version?

****
Sub ADP_ExportExcel()
'transpose horizontal column output (multiCol) into single column
(vertical)
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range
With Sheets("sheet1")
Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
<<
End With
Set rngTo = Sheets("sheet2").Range("A1")
For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value '2 columns
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value '3 columns
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value '4 columns
Set rngTo = rngTo.Offset(4, 0) 'number of rows per item
Next rng
End Sub

--
AncientLearner
...never to old to learn something new

macro OK in Excel 2003 but NOT in Excel 2000
 

Thanks, that seems to have worked.
--
AncientLearner
....never to old to learn something new


"AncientLearner" wrote:

I can run to following code in Excel 2003 but when I run it in Excel 2000 I
get an error/debug at the line Set rngFrom = <<<

Should I declare different variables because of 2000 version?

****
Sub ADP_ExportExcel()
'transpose horizontal column output (multiCol) into single column
(vertical)
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range
With Sheets("sheet1")
Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) <<
End With
Set rngTo = Sheets("sheet2").Range("A1")
For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value '2 columns
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value '3 columns
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value '4 columns
Set rngTo = rngTo.Offset(4, 0) 'number of rows per item
Next rng
End Sub

--
AncientLearner
...never to old to learn something new

macro OK in Excel 2003 but NOT in Excel 2000
 

Your suggestion put the final resolution to another problem and really solved
my issue. Thanks
--
AncientLearner
....never to old to learn something new


"Peter T" wrote:

I don't see anything wrong in your code that would fail due to version.

However your code would fail if the activesheet is not a worksheet, eg it's
a chart-sheet or no visible sheet. Just in case, it might be worth the two
changes to the commented line below -

With Sheets("sheet1")
' Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
Set rngFrom = .Range(.Range("A1"), .Cells(.Rows.Count, 1).End(xlUp))

Regards,
Peter T


"AncientLearner" wrote in message
...
I can run to following code in Excel 2003 but when I run it in Excel 2000
I
get an error/debug at the line Set rngFrom = <<<

Should I declare different variables because of 2000 version?

****
Sub ADP_ExportExcel()
'transpose horizontal column output (multiCol) into single column
(vertical)
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range
With Sheets("sheet1")
Set rngFrom = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
<<
End With
Set rngTo = Sheets("sheet2").Range("A1")
For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value '2 columns
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value '3 columns
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value '4 columns
Set rngTo = rngTo.Offset(4, 0) 'number of rows per item
Next rng
End Sub

--
AncientLearner
...never to old to learn something new