Dynamic range in VBA with Excel functions

BigBeachBananas

Active Member
Joined
Jul 13, 2021
Messages
450
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
Excel Formula:
=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:
VBA Code:
Dim sourceRange as Range
Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(data_date_range) + 1, _
                                Application.WorksheetFunction.Columns(InputClaims))
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Further development:

The "Object doesn't support this property or method" error is due to this part...
VBA Code:
...Application.WorksheetFunction.Columns(Range("InputClaims"))

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

The "Object doesn't support this property or method" error is due to this part...
VBA Code:
...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:

VBA Code:
Range("InputClaims").Columns.Count
 
Upvote 0
Try this:

VBA Code:
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").

VBA Code:
Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(Range("data_date_range")) + 1, _
                                Range("InputClaims").Columns.Count
 
Upvote 0
Solution
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").

VBA Code:
Set sourceRange = Range("AC22").Resize(Application.WorksheetFunction.CountA(Range("data_date_range")) + 1, _
                                Range("InputClaims").Columns.Count
Perfect. Thanks!
 
Upvote 0

Forum statistics

Threads
1,224,847
Messages
6,181,323
Members
453,032
Latest member
Pauh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top