PDA

View Full Version : Why the Set temp(0) = Activesheet.Columns(3).insert is not working?



prabhafriend
07-18-2011, 02:54 AM
The insert method won't return a range object? Please don't suggest any other alternatives to track the newly inserted column because that's not the subject.

Bob Phillips
07-18-2011, 03:52 AM
Because it doesn't work that way, you want it to work as you think it should, rather than work with the way it does.

prabhafriend
07-18-2011, 05:03 AM
Thanks xld.

CatDaddy
07-19-2011, 10:13 AM
jeez...if i could see the code i would be willing to help :)

Paul_Hossler
07-19-2011, 04:17 PM
As XLD says, VBA doesn't work that way, so I think you're going to have to accept alternatives



Please don't suggest any other alternatives to track the newly inserted column because that's not the subject.



Option Explicit

Sub drv()
Dim temp(0 To 10) As Range
Set temp(0) = ReturnRangeAfterInsert(3)
MsgBox temp(0).Address
End Sub
Function ReturnRangeAfterInsert(n As Long) As Range
ActiveSheet.Columns(n).Insert
Set ReturnRangeAfterInsert = ActiveSheet.Columns(n + 1)
End Function


Paul