Dynamic range in VBA with Excel functions

[BigBeachBananas]

Hi,

I'm trying to set the sourceRange as a dynamic range in VBA that starts from cell AC22 and offset with the height of COUNTA(data_date_range)+1, where data_date_range is a named range.
and width of Columns(InputClaims), where InputClaims is also a named range.

The following formula works in excel:

=OFFSET(AC22,,,COUNTA(data_date_range)+1,COLUMNS(InputClaims))

This doesn't work in VBA and getting "Object doesn't support this property or method" error:

Dim sourceRange as Range
Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(data_date_range) + 1, _
                                Application.WorksheetFunction.Columns(InputClaims))

 

[davesexcel]

possibly :

range("data_date_range")

Same for the other range

 

[BigBeachBananas]

possibly :

range("data_date_range")

Same for the other range

Same error.

 

[BigBeachBananas]

Further development:

The "Object doesn't support this property or method" error is due to this part...

...Application.WorksheetFunction.Columns(Range("InputClaims"))

Why Columns isn't supported or is there an alternative to count # of columns of a named range?

 

[dreid1011]

Further development:

The "Object doesn't support this property or method" error is due to this part...

...Application.WorksheetFunction.Columns(Range("InputClaims"))

Why Columns isn't supported or is there an alternative to count # of columns of a named range?

Try this:

Range("InputClaims").Columns.Count

 

[dreid1011]

Try this:

Range("InputClaims").Columns.Count

And maybe change your references to Range("named range") like below. I was getting an inaccurate count of rows using just CountA("data_date_range").

Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(Range("data_date_range")) + 1, _
                                Range("InputClaims").Columns.Count

 

[BigBeachBananas]

And maybe change your references to Range("named range") like below. I was getting an inaccurate count of rows using just CountA("data_date_range").

Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(Range("data_date_range")) + 1, _
                                Range("InputClaims").Columns.Count

Perfect. Thanks!

 

[dreid1011]

Perfect. Thanks!

You're welcome.