Transpose dynamic range

[Michael Beckinsale]

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

 

[BrianB]

I think your last line should be :-

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

 

[Tom Ogilvy]

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

should be

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


Regards,
Tom Ogilvy

 

[Michael Beckinsale]

Thanks guys, now works a treat !

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

should be

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


Regards,
Tom Ogilvy


column