Results 1 to 2 of 2

Thread: Reference another page as variable in formula VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Reference another page as variable in formula VBA

    Hi All,

    I'm trying to enter the formula =average($B$" & ProductRow & ":$KL$" & ProductRow & ") into cell "Cells(PivotRow, 3) onto my page "Pivot Data". My Line of code is the following

    Cells(PivotRow, 3).Formula = "=average($B$" & ProductRow & ":$KL$" & ProductRow & ")"

    This works except for the fact that the data i'm trying to average can be on several different pages (For which i have used the variable "CurrentProduct") How do i include this variable sheet name in my formula? I have already tried the following which gave an error message

    Cells(PivotRow, 3).Formula = "=average(" & CurrentProduct!$B$" & ProductRow & ":$KL$" & ProductRow & ")"

    Many thanks in advance
    Last edited by Christian S; Aug 7th, 2019 at 04:13 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,234
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Reference another page as variable in formula VBA

    How about
    Code:
    "=average('" & CurrentProduct & "'!$B$" & ProductRow & ":$KL$" & ProductRow & ")"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •