PDA

View Full Version : Solved: Help with resize



xluser2007
12-02-2008, 06:10 PM
Hi All,

I am doing the following:

Option Explicit

Sub test()

ThisWorkbook.Worksheets("Expected").Range("T1").Resize(1, -16).calculate

End Sub
That is, select the relevant region with respect to T1 and calculate it.

However, I will be requiring to calculate this many times in a loop and wanted to SET this calculation range in the macro as follows:

Option Explicit

Sub test()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Expected").Range("T1").Resize(1, -16)

' Then do more steps here and then recalculate range

rng.Calculate

End Sub
The red throws an '1004' error Application defined or object defined error.

Ani ideas on how to correct for this as optimally as possible (i.e. without selecting the rng)?

Any help appreciated.

Zack Barresse
12-04-2008, 11:47 AM
You have to resize from the other direction (right/down), so just Offset it to the left, then resize to the right...

Option Explicit

Sub test()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Expected").Range("T1").Offset(0, -15).Resize(1, 16)

' Then do more steps here and then recalculate range

rng.Calculate

End Sub

HTH

xluser2007
12-04-2008, 03:14 PM
You have to resize from the other direction (right/down), so just Offset it to the left, then resize to the right...

Option Explicit

Sub test()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Expected").Range("T1").Offset(0, -15).Resize(1, 16)

' Then do more steps here and then recalculate range

rng.Calculate

End Sub
HTH

Works a treat Zack, appreciate your help :)!