PDA

View Full Version : [SOLVED] Range(Cells()) and the 1004 Error



thk12205
05-29-2018, 06:10 PM
Version: Excel 2013 Professional Plus

I'm attempting to use information from another sheet to enact an If statement, however I seem to be bumping into Run-time error 1004 "Application-defined or object-defined error".

I tried to qualify cells in the range, as well as the range, but it seems to still snag the 1004.

How can I fix this?



Sub Test1() 'only Cells()
MsgBox ActiveWorkbook.Worksheets("Sheet3 Background Info").Cells(1, "N").Value
End Sub


Sub Test2() 'only Range()
If Worksheets("Sheet3 Background Info").Range("N1").Value <= Date Then
MsgBox "Works"
End If
End Sub


Sub Test3() 'combined Range(Cells())
If Worksheets("Sheet3 Background Info").Range(ActiveWorkbook.Worksheets("Sheet3 Background Info").Cells(1, "N")) <= Date Then
MsgBox "Works"
End If
End Sub

In Test 1 and 2, both Cells() and Range() function properly.

In Test 3, which combines Range(Cells()), it does not seem to work.

Paul_Hossler
05-29-2018, 07:31 PM
Not too sure about what you're doing, but the part in bold is (I think) returning the .Value of whatever is in N1, and probably can't be interpreted by the .Range




Sub Test3() 'combined Range(Cells())
If Worksheets("Sheet3 Background Info").Range(ActiveWorkbook.Worksheets("Sheet3 Background Info").Cells(1, "N")) <= Date Then
MsgBox "Works"
End If
End Sub



If N1 has the string "Z26" (for example), then you most like have this to evaluate



Sub Test3() 'combined Range(Cells())

If Worksheets("Sheet3 Background Info").Range("Z26") <= Date Then
MsgBox "Works"
End If

End Sub




What is wrong with Test2 that you don't want to use it, and what are you trying to do?

thk12205
05-30-2018, 07:53 AM
Test 2 doesn't work unfortunately because I'm running a for loop to check several iterations, meaning I need to nest a Cells(#, "N") in order to run through each cell.

I'm trying to pull all the dates from whichever season today is. If it's February 15, it'll pull the dates from 1/1/18-3/31/18, and put those dates on the leftmost column. If it's December 8th, it'll pull 10/1/18-12/31/18.

The value being pulled by the Cells() is the date 1/1/18.

Paul_Hossler
05-30-2018, 08:00 AM
I thought Test2 was working, and it was only Test3 that wasn't


In Test 1 and 2, both Cells() and Range() function properly.

In Test 3, which combines Range(Cells()), it does not seem to work.


Since there seems to be additional requirements ...



because I'm running a for loop to check several iterations, meaning I need to nest a Cells(#, "N") in order to run through each cell



… it might be better to include a small sample workbook with a very good description of what you want to do

thk12205
05-30-2018, 08:26 AM
Update: I changed from Range(Cells(#,"N")) to Range("N1").Cells(1,1), and it seems to be working now.

If anyone knows the correct way to run the Range(Cells()) option, though, I would still love to know.

Paul_Hossler
05-30-2018, 11:55 AM
It seems like you're using case 1 below which seems to me to be more complicated

case 2 is a straight forward way to get a value from a cell

case 3 and 4 are common ways to iterate a block of cells




Option Explicit

Sub test()

Dim i As Long
Dim r As Range

'case 1
MsgBox ActiveSheet.Range("N1").Cells(1, 1).Address
MsgBox ActiveSheet.Range("N1").Cells(2, 1).Address


'case 2
MsgBox ActiveSheet.Range("N1").Address
MsgBox ActiveSheet.Range("N2").Address

'case 3
For i = 1 To 4
MsgBox ActiveSheet.Cells(i, 14).Address
Next i

'case 4
For Each r In ActiveSheet.Range("N1:N4").Cells
MsgBox r.Address
Next
End Sub



Without having a sample of your sheet and objectives, it's hard to offer any more

thk12205
05-31-2018, 10:15 AM
Thank you Paul. This is the information I needed.

I couldn't use 2 because the macro would be checking through roughly 30+ cells, which wouldn't be ideal writing out.

Although case 4 seems like a ringer, and I may be using that in future macros.