XL2007 VBA Transpose Using R1C1 References & Public Constants

[CiaranEire]

Hi, I can't figure out why the following won't work. I have adapted code posted by user VoG on the thread http://www.mrexcel.com/forum/excel-...-applications-copy-column-data-into-rows.html into my workbook as;

Sheet2.Range("A2").Resize(2928, 80).Value = Application.Transpose(Sheet5.Range("B101:DHQ180"))

It works perfectly. However, I get Runtime Error 1004: "Method 'Range' of object '_Worksheet' failed" when I try to use publicly declared constants for the references as in the following;

Public Const Sheet5StartRow As Integer = 101
Public Const Sheet5EndRow As Integer = 180
Public Const Sheet5StartCol As Integer = 2
Public Const Sheet5EndCol As Integer = 2929

Public Const Sheet2StartRow As Integer = 2
Public Const Sheet2EndRow As Integer = 2929
Public Const Sheet2StartCol As Integer = 1
Public Const Sheet2EndCol As Integer = 80

Sheet2.Cells(Sheet2StartRow, Sheet2StartCol).Resize(Sheet2EndRow - 1, Sheet2EndCol).Value = _
    Application.Transpose(Sheet5.Range(Cells(Sheet5StartRow, Sheet5StartCol), Cells(Sheet5EndRow, Sheet5EndCol)))

When I highlight the constants in the debugger it displays the values I expect. I have tested with all sheets/cells unhidden and unprotected and no luck. I can use the first code if needs be but if for no other reason than my own learning it would be good to know what's wrong with the second method.

Many Thanks,
Ciaran

 

[pgc01]

Hi Ciaran

You have a syntax error, you forgot to qualify the Cells() objects in the right side of the statement:

Sheet2.Cells(Sheet2StartRow, Sheet2StartCol).Resize(Sheet2EndRow - 1, Sheet2EndCol).Value = _
    Application.Transpose(Sheet5.Range([COLOR=#ff0000]Sheet5.[/COLOR]Cells(Sheet5StartRow, Sheet5StartCol), [COLOR=#ff0000]Sheet5.[/COLOR]Cells(Sheet5EndRow, Sheet5EndCol)))

 

[CiaranEire]

Syntax, syntax, syntax... Thank you kindly

 

[pgc01]

You're welcome. Thanks for the feedback.

Remark:

Notice that although the syntax is not correct in the general case, it still might work if at the time of the execution of the statement the active sheet is Sheet5. In that case it would work because, as you know, the range objects default to the active sheet. It is not, however, safe to count on it, it's better to have the complete statement.