News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Updating cell formulae using vba in a macro

    Posted on phproberts Comment on the AskWoody Lounge

    Home Forums AskWoody support Microsoft Office by version Questions: Microsoft Office Updating cell formulae using vba in a macro

    Tagged: , ,

    This topic contains 7 replies, has 4 voices, and was last updated by  RetiredGeek 7 months ago.

    • Author
      Posts
    • #243830 Reply

      phproberts
      AskWoody Lounger

      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

    • #243871 Reply

      Paul T
      AskWoody MVP

      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

    • #243873 Reply

      phproberts
      AskWoody Lounger

      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

    • #243884 Reply

      Paul T
      AskWoody MVP

      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.
    • #315772 Reply

      Anonymous

      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.

    • #315796 Reply

      RetiredGeek
      AskWoody MVP

      Peter,

      What you want is a Dynamic Range Name. 😎

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #315808 Reply

      Paul T
      AskWoody MVP

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

      cheers, Paul

    • #316362 Reply

      RetiredGeek
      AskWoody MVP

      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).

      DynamicRNMenu

      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

      Attachments:
      1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Updating cell formulae using vba in a macro

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.