Consulting

Results 1 to 4 of 4

Thread: Indirect Cell references from VBA gives error

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    2
    Location

    Indirect Cell references from VBA gives error

    I am using Excel 2010

    I am down to my last bit of code to finish my project. I needed to move this from a cell, to a macro .
    "C" is the static column, and cell G4 contains the row number, which is input by my users.

    This takes a current number (K4) and adds the number of items my user has input to be added to it (N3).
    This is a simple inventory spreadsheet and I can't believe I am stuck.

    Here is what I have currently, that errors with: 'Compile error: Sub or Function not defined' referencing INDIRECT within the RANGE selection line.

    -------------------------------
    Option Explicit
    dim rr as integer
    dim ss as integer
    dim add as integer

    Sub UpdateQuantityPositive()
    '
    ' Macro1 Macro
    '
    '
    rr = Range("K4").Value
    ss = Range("N3").Value
    add = ss + rr
    Range(INDIRECT("c" & G4)).Select
    ActiveCell.FormulaR1C1 = add

    End Sub

    ----------------------
    This is my first time posting and I have only done so since I can't find what I personally believe is going to be something I am looking too hard to find and should have known..
    I have searched the forum but nothing is indirectly referencing like I think I am doing... (sigh)
    Last edited by GeorgeC; 06-29-2018 at 06:39 AM. Reason: wrong variable...

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    You don't need INDIRECT at all in VBA but you can't refer to a cell using just an address like G4 - you need the Range property too:

    Range("C" & Range("G4").Value).FormulaR1C1 = add
    Be as you wish to seem

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Assuming that G4 contains the Row number where the result is to go

    You don't need the INDIRECT()



    Option Explicit
    
    dim rr as Long   '  Use Longs 
    dim ss as Long
    dim add as Long
    
    Sub UpdateQuantityPositive()
    
        rr = Range("K4").Value
        ss = Range("N3").Value
        
        add = ss + rr
    
        'I prefer to use Cells() for something like this -- your choice
        Cells(Range("G4").Value, 3).Value = add
    
    
    
    '    Range("c" & Range("G4").Value).Value = add
    
    
        
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    2
    Location
    I had a feeling I was over-thinking it...

    SOLVED! Thank you!!!

Posting Permissions

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