Updating cell formulae using vba in a macro

Topic

New Reply

Hi, has anybody had success with updating parts of formulae in a group of Excel cells by using VBA in a macro? I have a number of cells all in the same column on one worksheet that reference different ranges of cells on a source worksheet called ‘Current extract’. The ranges on the source worksheet each span all rows of that worksheet.

For example, one target cell has formula “=COUNTA(‘ Current extract’!D4:D286)”
Another target cell has formula “=COUNTIF(‘Current extract’!U4:U286,”P/S*”)”

I am trying to use a macro to automatically update the upper bound (286 in these examples) to whatever the new value of the source data last row happens to be each time it is refreshed. Any ideas would be appreciated.

Cheers,

Peter

Australian devotee

Reply | Quote

Replies

The easiest method would be to use a named range on Current extract, then you don’t need to update the formulas.

How do you know what the new upper bound is?

cheers, Paul

Reply | Quote

Hi Paul,

While on the source sheet (Current extract) I do:

newUpperBound = Cells(Rows.count, “D”).End(xlUp).Row

then wanted to use newUpperBound to update the instances where currentUpperBound existed in the various formulae on the target sheet (Summary).

Cheers,

Peter

Australian devotee

Reply | Quote

Use that code to set the named range, then it’s simple.
Something like:

MyWorksheet.Names.Add Name:=”myNamedRange”
Set myNamedRange = myWorksheet.Range(“A5”).End(xlUp).Row

cheers, Paul

1 user thanked author for this post.

phproberts

Reply | Quote

Just by using named ranges, you can still skip rows, unless you check to make sure you didn’t add rows at the bottom of the named range. For example, named range is A2:A100 and you insert 10 rows from A101:A110. Your named range will still be A2:A100 UNLESS you insert the 10 rows above row 100 which then pushes the end of the range down.

It might be easier to use an Excel Table. Any new rows added to the bottom of the table will be formatted, letting you know that they are in the table. You can just use the table naming convention to access the named range. This fix does not use VBA.

Doing it in VBA should be fairly easy. Start your counter on top row, count down to bottom of new range, then delete and assign named range again.

Reply | Quote

Peter,

What you want is a Dynamic Range Name.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

So you still need to define a maximum possible number of rows? Do you set it to 65535 to cover all possibilities?

cheers, Paul

Reply | Quote

Paul,

Personally, I have a macro in my Personal.xlsb file called CreateDynamicRangeName which when called pops up a menu allowing me to specify the max number of rows & the number of columns. If then Number of Rows is blank it automatically sets it to the number of rows in the workbook, different for 32 and 64 bit Excel, to the maximum row – the current row (starting row of the range).

By doing it this way I don’t have to remember the complex syntax for Dynamic Ranges and all entries in the dialog box are checked for out of bounds errors and you are also warned if the selected range name is already in use.

I almost forgot to mention that the workbook where the Dynamic Range Name is created does NOT have to allow macros as the macro to do this sits in the user’s Personal.xlsb or .xlsm file not in the file containing the created range name.

If anyone is interested I’d be glad to post a .frm file that can be used to import the form and code in one easy step.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

Paul T

Reply | Quote