PDA

View Full Version : [SOLVED] Indirect Cell references from VBA gives error



GeorgeC
06-29-2018, 06:38 AM
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)

Aflatoon
06-29-2018, 07:45 AM
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

Paul_Hossler
06-29-2018, 08:13 AM
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

GeorgeC
06-29-2018, 08:14 AM
I had a feeling I was over-thinking it...

SOLVED! Thank you!!!:yes:yes