Code generates error using variable in cell selection

fieldscd · Oct 27th 2010

Hello. I love the forums, and am new to posting. I keep getting error 1004 runtime error. It worked one day, but not now. HELP!!!!

Code

Option Explicit
Dim title As String
Dim ingred As String
Dim method As String
Dim rng As Range
Dim result As String
Dim xrow As Long
Private Sub Image1_Click()
If TextBox1.Value = "" Then
MsgBox "Whoops!  Please enter a recipe name!"
Exit Sub
End If
If TextBox2.Value = "" Then
MsgBox "Whoops!  Please enter ingredients!"
Exit Sub
End If
If TextBox3.Value = "" Then
MsgBox "Whoops!  Please enter a cooking method!"
Exit Sub
End If
title = TextBox1.Value
ingred = TextBox2.Value
method = TextBox3.Value
result = MsgBox(title & ": " & ingred & " - " & method, vbYesNo, "Verify Recipe!")
If result = vbNo Then Exit Sub
Sheets("Recipe").Activate
xrow = Range("A1").End(xlDown).Row
xrow = xrow + 1
Sheets("Recipe").Cells(xrow, 1).Select
ActiveCell.Value = title
ActiveCell.Offset(0, 1).Value = ingred
ActiveCell.Offset(0, 2).Value = method
MsgBox "Recipe has been added!"
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox1.SetFocus
End Sub
Private Sub Image3_Click()
Unload Me
frmViewRecipe.Show
End Sub

Display More

**Trebor76** · Oct 28th 2010

Re: Runtime Error 1004

Hi fieldscd,

Welcome to the OzGrid community!!

Though it's impossible to suggest an accurate answer as you have not said which line of code is causing the issue, at a guess I'd say there's no data in Column A of the Recipe tab to set the xrow variable for.

If not, post back with a sample of the workbook attached and I'll take a further look.

Regards,

Robert

AAE · Oct 28th 2010

Re: Runtime Error 1004

fieldscd,

Please revise your thread title to describe your objective. Because thread titles are the key component of searching the forum, "Runtime Error 1004" is too generic to be of help to anyone searching for a solution to a similar need. It would best to explain what your code is intended to accomplish so we can better understand where the issue may lie.

To edit your thread title:
1. Click the EDIT POST button below your original post
2. Click Go Advanced
3. Edit the title

Open the VB Editor and step through the code using the F8 key, then post back about where the failure occurs.

fieldscd · Oct 28th 2010

Re: Runtime Error 1004

My apologies (newbie)
the errors always highlight the following code:

Code

Sheets("Recipe").Cells(xrow, 1).Select

I have multiple other forms using the same coding for bills, doctors, etc. that work fine; is there a max number of forms per workbook or something?

I did have values in "A1". I can copy the workbook and upload, but I have sensitive information in the sheets that I'd rather not pass on (phone numbers, financial info, etc.).

Rob Xaos · Oct 28th 2010

Re: Runtime Error 1004

Is "Recipe" the correct name of the sheet? Maybe it should be "Receipt" ? It either has to be the sheet name or xrow. There needs to be a value in A1 and A2 otherwise:

Code

xrow = Range("A1").End(xlDown).Row
xrow = xrow + 1

Will result in xrow being set to last possible row + 1 which will cause 1004 run-time.

AAE · Oct 28th 2010

Re: Runtime Error 1004

Please code tags when posting code to the forum. They are required. I've added them for you this time only. To lean how to use tags, click the tags link in my signature.
Code tags are applied like so: [noparse]

Code

your code

[/noparse]

Also, do not quote entire posts. Quote only the relevant part of the post and only if quoting add clarity for your reply. Thanks. It makes for a cleaner, easier to follow thread.

Quote

I have sensitive information in the sheets that I'd rather not pass on

You can always mock up a sample using dummy data.

Last item: the new thread title is still insufficient as it is too generic to get a specific answer when searching. Note the change to your title based on what I can understand about your thread per the subsequent postings.

**Trebor76** · Oct 28th 2010

Re: Code generates error using variable in cell selection

Quote

I did have values in "A1"

Yes, and I'd say that's the only value in column A. It's bad practice to try and assign a row value by going down a Column to find the last row as you can often find that the value is more than the number of rows on a spreadsheet so it cannot be referred to (and henece the Runtime Error 1004 message) which I'm fairly sure is happening here.

Once you change the thread title as AAE has requested, I'll post a possible solution.

Regards,

Robert

fieldscd · Oct 29th 2010

Re: Runtime Error 1004

Adding info in both cells A1 and A2 fixed it! Thanks so much!

[COLOR="red"]MODERATOR:[/COLOR] fieldscd, read my comments in post #6 about quoting entire posts in your reply. Once again, the unnecessary quote has been deleted. Do not ignore Moderator requests.

**mikerickson** · Oct 29th 2010

Re: Code generates error using variable in cell selection

If there are formulas in column A that evalutate to "", its possible that xrow might be a string or < 1.

What does the Immediate Window give as xrow's value when it errors?

Code generates error using variable in cell selection

Participate now!

Share