locked
Integers as cell reference returns #NAME error RRS feed

  • Question

  • 
    

    Hiyo,

    I have a macro that autopopulates data in a single row from a different sheet, the title of which is in column A.  it goes a little something like this:

    Sub AutoPopulateDashboard()
    '
    ' AutoPopulateDashboard Macro
    ' Automatically populates dashboard with relevant data from individual sheets
    '
    
    '
        With Worksheets("Dashboard")
            .Range("B6") = "=INDIRECT(""'""&A6&""'!D2"")"
            .Range("C6") = "=INDIRECT(""'""&A6&""'!A3"")"
            .Range("D6") = "=INDIRECT(""'""&A6&""'!B3"")"
            .Range("E6") = "=INDIRECT(""'""&A6&""'!C3"")"
            .Range("F6") = "=INDIRECT(""'""&A6&""'!F2"")"
        End With
    End Sub

    I'm now trying to write a macro that will run this single formula on each row, with a different value next to each Column heading (the letters) each time.  I've gotten this far:

    Sub NestedAutopopulateLoop()
     '
     ' NestedAutopopulateLoop Macro
     ' This macro will run the autopopulate macro on each new row, until a blank cell is found in the phone number list
     '
     x = 5
     
        Do While Cells(x, 1).Value <> ""
            With Worksheets("Dashboard")
                .Range("B" & x) = "=INDIRECT(""'""&Ax&""'!D2"")"
                .Range("C" & x) = "=INDIRECT(""'""&Ax&""'!A3"")"
                .Range("D" & x) = "=INDIRECT(""'""&Ax&""'!B3"")"
                .Range("E" & x) = "=INDIRECT(""'""&Ax&""'!C3"")"
                .Range("F" & x) = "=INDIRECT(""'""&Ax&""'!F2"")"
            End With
        x = x + 1
     Loop
     
    End Sub

    However this returns a #NAME error.  Going through the debug/evaluation steps reveals that the

    &Ax&

    causing the #NAME error, but I don't know how to correct this?  otherwise the code runs perfectly.  The ("B" & x) portion works perfectly, but the &Ax& part is failing.

    help please? :D

    Thanks everyone

    
    

    Thursday, October 17, 2013 11:44 PM

Answers

  • OK, I think I understand what you want.

    Try:

    Range("B" & x).Formula = "=indirect(""'"" & A" & x & " & ""'!D2"")"

    or, perhaps simpler, since you always want to refer to the cell one column to the left:

    Cells(x, "B").FormulaR1C1 = "=INDIRECT(""'"" & RC[-1] & ""'!D2"")"


    Ron

    Monday, October 21, 2013 2:17 PM

All replies

  • You have to concatenate with x, just as you do for "B" & x:

                .Range("B" & x) = "=INDIRECT(""'""&A" & x "&""'!D2"")"
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 18, 2013 9:10 AM
  • Since you don't need the single quotes since the worksheet name has no spaces, you could use something like:

    .Range("B" & x) = "=INDIRECT(""A" & x & "!D2"")"

    or, if you must have the single quotes:

    .Range("B" & x) = "=INDIRECT(""'A" & x & "'!D2"")" 


    Ron


    Friday, October 18, 2013 11:24 PM
  • Hi Hans,

    Thank you very much for your reply :) I would've replied sooner, but your friday was my saturday, first day back at it today.

    When trying your solution, VBA returns a syntax error, with a bit of the inbuilt debug (I'm not very good at this yet, obviously) evidently the code can now be interpreted two ways, and VBA doesn't know which we want.

    This is now well past my coding knowledge, and if theres anything further you can think of, I would place even money on myself being eternally grateful :)

    EDIT:  Ironically, I was tweaking the solution provided by Ron to fix the #REF! error, and I felt so close! The formula was almost working, it needed one final addition, and then boom, syntax error.

    I checked.  I had "found" the solution you had proposed. :P

    Sunday, October 20, 2013 10:58 PM
  • Hi Ron,

    Thank you very much for your  reply :)  I currently have spaces in my sheet titles, as they're phone numbers in the #### ### ### format, however I did try removing the spaces, and also tried with the spaces, and both your solutions return a #REF! error.

    The formula works through to :

    =INDIRECT("'A5'!D2")
    
    

    But what we need to end up with is:

    =INDIRECT("'"&A5&"'!D2")

    Looking on the bright side of life (and we always should!) the formula is now correctly using X, and the X + 1 loop correctly, each row shows A5, 6, 7 etc.  I think we're close here!

    Thanks again for all the help, can I push my luck and ask for one last bit of help with this #REF! error? I'm really struggling to figure this out :)

    Sunday, October 20, 2013 11:06 PM
  • OK, I think I understand what you want.

    Try:

    Range("B" & x).Formula = "=indirect(""'"" & A" & x & " & ""'!D2"")"

    or, perhaps simpler, since you always want to refer to the cell one column to the left:

    Cells(x, "B").FormulaR1C1 = "=INDIRECT(""'"" & RC[-1] & ""'!D2"")"


    Ron

    Monday, October 21, 2013 2:17 PM
  • Ladies and Gentlemen,

    We have a winner.

    Ron, you're a champion.  Thank you, and thank you Hans, for your time and help, its truly appreciated :)

    Monday, October 21, 2013 10:21 PM