1004 Error Setting Variable Equal to Value of a Range

[stinkingcedar]

Hey guys,

So I keep getting a 1004 on this particular line of code

newVal = sht.Range(Cells(4 + (30 * i), j)).Value

Sht is a worksheet variable set to one specific sheet in the workbook, newVal has been declared as double, i and j have both been declared as integers. Keeps telling me the range method failed.

 

[JLGWhiz]

Hey guys,

So I keep getting a 1004 on this particular line of code

newVal = sht.Range(Cells(4 + (30 * i), j)).Value

Sht is a worksheet variable set to one specific sheet in the workbook, newVal has been declared as double, i and j have both been declared as integers. Keeps telling me the range method failed.

error 1004 is a catch-all type error, usually related to a user created condition such as typos, or misuse of variables. You will need to post more of your code than the one line for analysis.

 

[stinkingcedar]

error 1004 is a catch-all type error, usually related to a user created condition such as typos, or misuse of variables. You will need to post more of your code than the one line for analysis.

Sorry about that, here's a longer piece of the code:

Public Sub Generate()

Dim StartCellRow As Integer, StartCellColumn As Integer
Dim i As Integer, newVal As Double, curVal As Double
Dim c As Integer, r As Integer, j As Integer
Dim Start As Range
Dim shtS As Worksheet
Dim shtDWorksheet
Dim shtV Worksheet
Dim Region As String, ProjectName As String


Set shtS= Sheets("Sheet1")
Set shtD= Sheets("Sheet 2")
Set shtV= Sheets("Sheet 3")


Application.ScreenUpdating = False


Region = shtV.Range("C1").Text


Select Case Region


        Case Is = "Americas"
            
            For c = 4 To 13
               
                j = 5
                    
                    For r = 4 To 24
        
                        For i = 0 To 29
                
                            newVal = shtD.Range(Cells(4 + (30 * i), j)).Value
                            curVal = newVal + curVal
                    
                        Next i
                        
                        shtS.Cells(r, c) = curVal
                        j = j + 1
                        
                    Next r
                
            Next c

The error is this line of code:

newVal = shtD.Range(Cells(4 + (30 * i), j)).Value

 

[Joe4]

Is this inconsistency really correct?

Set shtS= Sheets("Sheet1")
Set shtD= Sheets("Sheet 2")
Set shtV= Sheets("Sheet 3")

So, some of your sheet names have spaces in them and others do not?

 

[stinkingcedar]

Is this inconsistency really correct?

Set shtS= Sheets("Sheet1")
Set shtD= Sheets("Sheet 2")
Set shtV= Sheets("Sheet 3")

So, some of your sheet names have spaces in them and others do not?

No, I have renamed several variables and sheet names for the purpose of posting in this thread. Must have been a mistake when typing up the forum post, but in my code that is not the case.

 

[Joe4]

Often times, when I see that error, it is because it is unable to access range you are referencing. This can happen for a myriad of reasons, including:
- You have a typo in a sheet name (so you are trying to reference a sheet which does not exist)
- You are trying to reference an invalid cell reference (i.e. row 2,000,000)
- You are trying to reference a hidden cell
- You are trying to update a protected cell

Sometimes, stepping through your code and seeing what you variables are set to at certain points makes it evident what the error is. Have you tried that?

 

[Rick Rothstein]

The error is this line of code:

newVal = shtD.Range([B][COLOR="#FF0000"]shtD[/COLOR][/B].Cells(4 + (30 * i), j)).Value

I think the problem is the Cells property is referencing the active sheet which I am assuming is not what shtD is referencing. Try making the change I show in red and see if that solves the problem.

 

[stinkingcedar]

Joe, just attempted to step through the code and could not seem to glean any important information from doing so. All the other variables seem to be declared, set and passed correctly, so I can't seem to figure out why that line isn't working.

Rick, I just attempted your suggested change and still no luck :/

 

[Joe4]

Any chance you could post a sample of your data so we can try to recreate your scenario and try it out for ourselves?

There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

 

[Norie]

Does this work?

newVal = sht.Cells(4 + (30 * i), j).Value