VBA RunTime Error 1004: Method 'Range' of Object'_Worksheet' failed

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
Morning experts,

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count > 1 Then Exit Sub
        If Not Intersect(.Cells, Me.Range("C2:C1199")) Is Nothing Then
        .Offset(0, -1).Value = Date        '''''' or  Now <- Just a note: Now() will include date + time
       End If
   If Not Intersect(.Cells, Me.Range("I2:I1199")) Is Nothing Then

        Target.Offset(0, -6).Copy Destination:=Sheets("INCOME TOTALS").Range("AD4")
        Target.Offset(0, -5).Copy Destination:=Sheets("INCOME TOTALS").Range("AE4")
        Target.Copy Destination:=Sheets("INCOME TOTALS").Range("AF4")
    With Range(Range("AG4").Value)
        .Value = .Value + Range("AF4").Value
  End With
        
End If
End With
End Sub

Run time error stops on With Range(Range("AG4").Value)
AG4 contains the address of a cell that I want the value in AF4 to be added to.
I'd be grateful for any help you could give on how to straighten this out
grf
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
It looks like it should just be...
Code:
With Range("AG4")
not
Code:
With Range(Range("AG4").Value)
 

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
Thank you Mark 858. That has stopped the Run Time error but although the cursor ends up on the cell whose address is in AG4, the value of AF4 is not added to that cell. Regards grf
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
If AG4 does contain a valid cell address it should be Range(Range("AG4").Value).

Are you sure AG4 contains a valid cell address when you get the error?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

If I run
Code:
Sub xxx()
  With Range("AG4")
        .Value = .Value + Range("AF4").Value
  End With

End Sub

with 9 in AF4 and 8 in AG4 then AG4 changes to 17.

So...

A) what does it give you?
B) if you put in 2 empty cells the formulas =ISNUMBER(AF4) and ISNUMBER(AG4) what do you get?
c) if you run the below what do you get when you run the code below

Code:
Sub xxx2()
  With Range("AG4")
        .Value = Application.Sum(.Value, Range("AF4").Value)
  End With

End Sub

D) is the sheet with the code the same sheet that you have the cells to sum in (or is it a different sheet called INCOME TOTALS)?


but although the cursor ends up on the cell whose address is in AG4

Btw, that part of the code doesn't move the cursor anywhere (nor does any other part of the code posted at first glance) and where the cursor is has no relevance to what cells you are trying to sum.

Edit:
If AG4 does contain a valid cell address it should be Range(Range("AG4").Value).

Are you sure AG4 contains a valid cell address when you get the error?

That I didn't think of :( Thanks for pointing it out.
 
Last edited:

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
Thanks for replying Norrie. Yes AG4 most certainly contains a valid cell address. AD4 and AE4 provide a Name and Month respectively and in AG4 is the Formula =ADDRESS(MATCH(AD4,People,0),MATCH(AE4,longmonths,0)). This gives me the cell address in which to add the value of AF4
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

What exactly does AG4 show? and what exactly do AD4 and AE4 show?
 

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
AD4 shows Caroline. AE4 shows September AF4 shows 17.00 and AG4 with a formula of =ADDRESS(MATCH(AD4,People,0),MATCH(AE4,longmonths,0)) shows $J$1. And I'm trying to get the 17.00 in AF4 added to $J$1
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I'm afraid with that set-up (see below) I am not getting an error with the code below and so I don't think that I can be any help here (please note that this is with all the cells on the same sheet as the code resides in, if this isn't the case then you need to state what cells are on what sheets).

Code:
Sub xxx()
  With Range(Range("AG4"))
        .Value = .Value + Range("AF4").Value
  End With
End Sub
Excel Workbook
JKAFAG
146.00
2
3
417.00$J$1
Sheet1
Excel 2010
Cell Formulas
RangeFormula
AG4=ADDRESS(1,10,1,1)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
(please note that this is with all the cells on the same sheet as the code resides in, if this isn't the case then you need to state what cells are on what sheets).

Please note that the statement above includes J11.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,188
Members
417,259
Latest member
gtacw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top