Transpose dynamic range
 

Hi All,

I have a worksheet where l need to create columns dependent on values in
column A where the range is dynamic. After determining the range in column A
i then need to transpose the associated descriptions in column B.

In the code below all works fine to determine the range to transpose etc but
the problem is the last line of code which always produces #NAME.

Is there a problem using variables in the Transpose function ?

Sub create_columns()

Dim startcell
Dim endcell
Dim columncount

Sheets("CAB").Select
Range("A26").Activate
startcell = ActiveCell.Address
Range("A26").Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Do Until ActiveCell.Value 2
ActiveCell.Offset(1, 0).Activate
Loop

ActiveCell.Offset(-1, 0).Activate
endcell = ActiveCell.Address
columncount = Range(startcell, endcell).Count
Range("E20").Activate
Range(ActiveCell, ActiveCell.Offset(0, columncount - 1)).Select
Selection.FormulaArray = "=TRANSPOSE(startcell:endcell)"

All help / suggestions gratefully received

Regards

Michael Beckinsale

Transpose dynamic range
 

I think your last line should be :-

Selection.FormulaArray = "=TRANSPOSE(" &startcell &":" & endcell & ")"


---
Message posted from http://www.ExcelForum.com/

Transpose dynamic range
 

Selection.FormulaArray = "=TRANSPOSE(startcell:endcell)"

should be

Selection.FormulaArray = "=TRANSPOSE(" & startcell & ":" _
& endcell & ")"


Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
Hi All,

I have a worksheet where l need to create columns dependent on values in
column A where the range is dynamic. After determining the range in column
A
i then need to transpose the associated descriptions in column B.

In the code below all works fine to determine the range to transpose etc
but
the problem is the last line of code which always produces #NAME.

Is there a problem using variables in the Transpose function ?

Sub create_columns()

Dim startcell
Dim endcell
Dim columncount

Sheets("CAB").Select
Range("A26").Activate
startcell = ActiveCell.Address
Range("A26").Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Do Until ActiveCell.Value 2
ActiveCell.Offset(1, 0).Activate
Loop

ActiveCell.Offset(-1, 0).Activate
endcell = ActiveCell.Address
columncount = Range(startcell, endcell).Count
Range("E20").Activate
Range(ActiveCell, ActiveCell.Offset(0, columncount - 1)).Select
Selection.FormulaArray = "=TRANSPOSE(startcell:endcell)"

All help / suggestions gratefully received

Regards

Michael Beckinsale

Transpose dynamic range
 

Thanks guys, now works a treat !
"Tom Ogilvy" wrote in message
...
Selection.FormulaArray = "=TRANSPOSE(startcell:endcell)"

should be

Selection.FormulaArray = "=TRANSPOSE(" & startcell & ":" _
& endcell & ")"


Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
Hi All,

I have a worksheet where l need to create columns dependent on values in
column A where the range is dynamic. After determining the range in
column
A
i then need to transpose the associated descriptions in column B.

In the code below all works fine to determine the range to transpose etc
but
the problem is the last line of code which always produces #NAME.

Is there a problem using variables in the Transpose function ?

Sub create_columns()

Dim startcell
Dim endcell
Dim columncount

Sheets("CAB").Select
Range("A26").Activate
startcell = ActiveCell.Address
Range("A26").Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Do Until ActiveCell.Value 2
ActiveCell.Offset(1, 0).Activate
Loop

ActiveCell.Offset(-1, 0).Activate
endcell = ActiveCell.Address
columncount = Range(startcell, endcell).Count
Range("E20").Activate
Range(ActiveCell, ActiveCell.Offset(0, columncount - 1)).Select
Selection.FormulaArray = "=TRANSPOSE(startcell:endcell)"

All help / suggestions gratefully received

Regards

Michael Beckinsale