Run-time error '1004'. Method 'Range' of object '_Global' failed

**mjj347** · 02-23-2012, 02:04 PM

Hey everyone,
I seem to be having an error with how I am assigning variables and after reading through articles on the Range object I have not figured out where I went wrong. The error highlights 'Set rngName = Range(nextrow, "A")'

Here is my code:

Please Login or Register  to view this content.

The macro is designed to export values from a form into a reporting workbook, and then copy the entire original form into an employee file.
Thanks, I really appreciate the help as always.

**swoop99** · 02-23-2012, 02:22 PM

try

Please Login or Register  to view this content.

**mjj347** · 02-23-2012, 02:51 PM

Thank you very much, I made that change but the error has changed to 'Application-defined or object-define error' 9 lines later with:

Please Login or Register  to view this content.

**mjj347** · 02-23-2012, 03:06 PM

I also tried to add a temp variable to hold the cell address. While debugging I verified that temp = "A8" so rngName should be assigned that value. But the error I mentioned in the post above persists. Here is what I tried (the line highlighted by the error is bolded and underlined:

Please Login or Register  to view this content.

**swoop99** · 02-23-2012, 05:35 PM

ok, your problem here seems to be trying to interchange the cells method of address and the range method of address. They are not interchangable. ie, if you address a cell thusly:

Please Login or Register  to view this content.

then to address the same cell using the cells method you would use:

Please Login or Register  to view this content.

One uses the Excel column and row (letter and number) and the other uses numerical values with the row addressed first. So.....if you have a variable set to cell "a10" then using that variable in a cells command results in you trying to input something like:

Please Login or Register  to view this content.

which is a syntax error.

Geddit?

**mjj347** · 02-23-2012, 06:05 PM

That makes sense, thank you. And yes, I have no idea about VBA syntax. I have just been trying to find code that is close and adapt it, which leaves me in trouble at times. I have only been working with it for a week now :/.
So would 'pastespecial' work the same way with the range object?
The reason I am trying to do this is I started with code like this:

Please Login or Register  to view this content.

And so on for about 100 iterations. The problem is if I need to insert a new segment then I have to manually increment every column reference after it.
So I wanted to use an offset function to that I can just pop a new segment in without messing the reporting sheet up.
I just plugged this in but its generating the same error as the title of this thread, and before I spend a ton of time debugging it I would like to know what I am trying to do is possible:

Please Login or Register  to view this content.

If these blocks are syntactically correct then I can focus on checking my variables or something else. If there is an easier way to do this, using the nextrow variable then that would be great. Thank you again for your help.

**swoop99** · 02-23-2012, 06:54 PM

Close.

You don't need the leading . on the range copy command or the leading ws. on the pastespecial command.

You also need to set the rngname variable to the address property of the cell rather than just add the offset to it. Like this:

Please Login or Register  to view this content.

And really, don't worry one little bit about asking any questions you need an answer to. This is exactly what this forum is for, to aid learners in....well, learning. I started exactly the same way as you, not that long ago. Give it a few months and you'll be answering questions like this for people too.