PDA

View Full Version : Run-time error '1004': Application-defined or object-defined error



El_Diabolo
11-21-2013, 03:45 AM
Hi All,

I am using Excel 2010, 32-bit.

I made a post yesterday about using "Quotes within Quotes", which was answered very neatly by "mancubus". Having been given a solution I then persevered, in an attempt to understand where I had gone wrong, with the original task of trying to use a variable within the OFFSET property, rather than having it hard-coded. I have now reached the point where the syntax I am using seems to be acceptable, but I am getting the dreaded '1004' error. Whatever is wrong here is beyond me. I enclose the code I am using below. The sheet is brand new, not protected and contains month names in H1 thru H12. Nothing else. The statement in the code which is commented out works, but the statement using the variable within the OFFSET causes the error. Any help would be very welcome and may just save my sanity.


Sub TestRange()Dim FRow As Integer
FRow = 1
Range("H" & FRow & ":" & "OFFSET(""H"" & FRow,0,0,COUNTA(H:H),1)").Select
' Range("H" & FRow & ":" & "OFFSET(H1,0,0,COUNTA(H:H),1)").Select
End Sub




Many thanks.

snb
11-21-2013, 04:55 AM
Sub M_snb()
[H1:H12] = Application.Transpose(Application.GetCustomListContents(4))
End Sub

El_Diabolo
11-21-2013, 06:26 AM
Thanks for your reply, snb, but it doesn't really address what I'm trying to do. Perhaps I should have given a fuller explanation, but I was trying to keep it clutter-free. The month names used are for test purposes only - the data could be anything. I apologise if I misled you there. Also, I'm not trying to reformat the data. The original statement using the OFFSET is as follows:


If Not Intersect(Target, Range("H8:OFFSET(H8,0,0,COUNTA(H:H),1)")) Is Nothing Then

Although this gives a fuller picture of the requirement I believe the range object section stands in its own right. That is why I isolated it in my original post. What I really want is to solve the problem of why I am getting the '1004' error, rather than an alternative way to do it, which I was given yesterday. Again, I apologise if my original post was misleading.

Many thanks and best regards.

Kenneth Hobs
11-21-2013, 08:24 AM
There is a difference between the VBA Range object's Offset method and the formula =Offset. When using the later, use VBA's Evaluate(). So you see, the problem is that you did not use Evaluate().

Tip: When building strings as you did or for Evaluate(), set the string and then you can use Debug.Print to see if you built the string properly.

I think something like this would suffice.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("H8", Range("H" & Rows.Count).End(xlUp))
If Intersect(Target, r) Is Nothing Then Exit Sub
MsgBox r.Address
End Sub

El_Diabolo
11-21-2013, 10:00 AM
Thank you for your prompt reply, Kenneth. I do apologise, but, unfortunately, I am now more confused than ever. As follows:

1. I am not aware of using the formula =Offset. I thought I was using the Range object's Offset method.
2. I have checked out, as best I can, the Evaluate function, but I don't understand what it does, or how you can tell (whatever it is that it does).
3. Again, as above, despite reading up on the Debug.Print feature I don't get it.
4. While it would be great to understand the above points, I'm not sure it would enlighten me as to why I am getting error '1004'. I just do not understand why the change I made to the Offset as shown in the line of code I provided doesn't work.

Sorry to be so thick, and I very much appreciate all the time I am being given by some very experienced forum members, but I just seem to be going round in circles.

Best regards.

Kenneth Hobs
11-21-2013, 03:35 PM
Check the Immediate Window after running code with Debug.Print.

Example showing concepts that I explained.

Sub Ken()
Dim s As String
s = "A1"
Debug.Print s

Range("A2").Value = 5
MsgBox Range(s).Offset(1).Value, vbInformation, "Range Offset Method"

Range("A2").Value = 6
s = "Offset(" & s & ",1,0)"
Debug.Print s
MsgBox Evaluate(s), vbInformation, "Evaluate Offset Method"
End Sub

El_Diabolo
11-21-2013, 04:30 PM
Thanks again, Kenneth. Much obliged. It is very helpful to now know how to use Debug and Evaluate. I'm sure they will both show their value in times to come. Unfortunately, I still seem to be too stupid to use them to good effect to answer my query. I enclose a small procedure to demonstrate exactly what I mean. I'm sure the solution will be very obvious to you, but I just don't see it.


Sub TestRange()Dim FRow As Integer, P As String
FRow = 1
' Range("H1" & ":" & "OFFSET(H1,0,0,COUNTA(H:H),1)").Select ' This works
' Range("H" & FRow & ":" & "OFFSET(H1,0,0,COUNTA(H:H),1)").Select ' This works
' Range("H" & FRow & ":" & "OFFSET(""H"" & FRow,0,0,COUNTA(H:H),1)").Select ' This DOES NOT work
P = """OFFSET(""H"" & FRow,0,0,COUNTA(H:H),1)"""
Debug.Print P
' Range("H" & FRow & ":" & P).Select ' This DOES NOT work

End Sub




Please forgive me for taking so much of your time.

Best regards.

Kenneth Hobs
11-21-2013, 05:47 PM
Just post a simple example workbook and show what you want rather than trying code that does not work and does not explain what you want.

Tips:
Select is seldom needed.
Variables that hold row number values should be dimmed as Long.

Take note of the method that I posted. The Range object can be defined in several ways. In my example, the first parameter defines the top left of a range. The 2nd part defines the bottom right of a range. The Range object's End method as I used it is like selecting the last cell in a column and pressing the End key and then Up key. It is like mouse selection of a Range in a way. Some may use End to go from one cell and then Down. That is fine but it stops at the first blank cell. Users often want to include blank cells in a Range.

Set r = Range("H8", Range("H" & Rows.Count).End(xlUp))

Using the CountA formula function might lead you astray I fear.

EirikDaude
11-22-2013, 02:29 AM
2. I have checked out, as best I can, the Evaluate function, but I don't understand what it does, or how you can tell (whatever it is that it does).

It tries to evaluate what the result of a worksheet-formula would be. E.g. evaluate("=sum(A1:A3)") would return the same value as if you typed the formula inside the parantheses into a cell in a worksheet.


3. Again, as above, despite reading up on the Debug.Print feature I don't get it.


Debug.Print() prints whatever is inside the parantheses to the Immediate-window, which you can show either by selecting View->Immediate Window or by pressing Ctrl+G. This can be useful for evaluating whether or not a string looks like you expect it to, before trying to paste it to a cell.

By combining these two you can both get the string you're putting into cell and the expected result of it printed to the immediate window:


Sub test()
Dim sFormula As String

sFormula = "=SUM(B1:B15)"
Debug.Print ("Formula: " + sFormula)
Debug.Print ("Result: " + CStr(Evaluate(sFormula)))
End Sub

El_Diabolo
11-22-2013, 03:24 AM
Kenneth: thank you again for your reply.

1. The only reason I used Select was to quickly test if the range statement was working. I otherwise do not use it.

2. Using integer for row numbers is a hangover from previous versions, when the row number value was more limited. I will use long in future.

3. I have taken note of the method you posted regarding the Range object. It clearly is much superior to what I was using, and more appropriate for my purposes. I am currently using something very similar which was given to me by another forum member, mancubus. (I mentioned this in post #1 above); and I take your point about using Counta. So a solution to my original request is already in place, i.e. how to replace the hard-coded version with a dynamic version.

4. When I used the few lines of code I provided to test the Range statement, the workbook and worksheet were new and the only content was month names in H1 thru H12. So that is why I did not post a sample workbook.

So, to summarise, the original request has been solved, but I would still like to know why error '1004' is appearing on a statement which, apparently, is syntactically correct. Simple as that. I could just ignore it since the code I am now using works perfectly well, but simply ignoring a system error is rather like burying one's head in the sand. So that is why I am seeking an answer.

Thanks for all your help. Best regards.

Elrik: Thank you for your very clear explanation. My debugging skills have just shot up by about 500%. Great. Thanks a lot.

Best regards to all.

EirikDaude
11-22-2013, 03:59 AM
Sub test()
Dim frow As Long
FRow = 1
Debug.Print ("H" & FRow & ":" & "OFFSET(""H"" & FRow,0,0,COUNTA(H:H),1)")
End Sub

Puts out "H1:OFFSET("H" & FRow,0,0,COUNTA(H:H),1)" in the immediate window for me, so that is probably why it's not working :)

As to why it doesn't translate the second FRow into the string, I have no idea :P Maybe someone else can help.

El_Diabolo
11-22-2013, 05:03 AM
Thanks, Eirik. At least I now know that the syntax IS correct, at least in theory. Weird though isn't it? Certainly the suggestion made by Kenneth is far better and that, or something similar, is the preferred method, so that perhaps explains why others have not encountered this problem. Thank you for help. Much appreciated. (And I promise not to mention the football.)

Best regards.