Using a named range in VBA code

**TripleC** · 02-25-2010, 12:54 AM

I am trying to write a simply code to copy some formulas down to the last row of newly imported data. I have a UDF named Lastrow() that calculates the last row of the new data. The formula is in a cell named FinalRow.

I have tried numerous ways of referencing the value of FinalRow with no luck. Below is my latest attempt that hopefully is clear enough that it completes the idea that I am working on.

Please Login or Register  to view this content.

Any help is appreciated.

**protonLeah** · 02-25-2010, 01:13 AM

I tried your code in a standard sheet module with no problem. What module is the code in?
What sheet does: valFinalRow = Range("namedrange").Value refer to?
Is it the same or different from the "Activesheet"?

**Paul** · 02-25-2010, 01:18 AM

Hi TripleC, welcome to the forum.

Please take a moment to read the forum rules and in the future be sure to wrap your code in CODE tags as shown below. I've done it for you this time.

Please Login or Register  to view this content.

[/noparse][/CODE]

Thank you.

**TripleC** · 02-25-2010, 01:32 AM

Thanks for the response. I have the code in a module not on the sheets code module; I probably should add an activesheet reference somewhere in there if I need this on another sheet too. However, I think I see what the problem is now. The LastRow() UDF seems to be reverting to a #value error. Here is that function:

Please Login or Register  to view this content.

I just now added the volatile declaration, it updated correctly. I ran the copy macro and it does work, however, the LastRow #value errored again. Probably not supposed to changed problems mid-thread, but that seems to be my problem now.

**TripleC** · 02-25-2010, 01:33 AM

Thanks, I saw your post after my reply, I will use the code formatting in the future.

**Richard Schollar** · 02-25-2010, 01:37 AM

HI

I wouldn't use this UDF - you don't need it as you can calculate the last row in your macro itself:

Please Login or Register  to view this content.

**TripleC** · 02-25-2010, 01:55 AM

Thanks for the reply.

I actually use the last row calculation in other in-sheet formulas. I can see that if I was stronger with VBA I could probably avoid the UDF. However, I worry that my learning curve won't accommodate my due date for this file.

I don't understand why it error's out.

**Richard Schollar** · 02-25-2010, 02:03 AM

You shouldn't need a UDF to calculate the last row as you can, should you desire, use native Excel formulas to do so. The problem with the UDF as it stands is that it refers to the activesheet and not the sheet on which the formula resides. This means as you move around sheet sin your workbook, the UDF refers to these newly activated sheets which means the value it returns will vary depending on which sheet is active. It is asking for trouble and should be avoided.

**TripleC** · 02-25-2010, 02:17 AM

Thanks again. Very convincing, I will look into native excel solution for this.

**protonLeah** · 02-25-2010, 03:14 AM

Please post some code where LastRow is called.
Also, your UDF does not declare its return value so that it defaults to Variant, it should be:

Please Login or Register  to view this content.

And, in the line

Please Login or Register  to view this content.

I'm not sure what sheet [A1] refers to.
If you pass the sheet name as a parameter to a function that returns a LONG it would look like:

Please Login or Register  to view this content.

Finally, your use of the FIND function seems to indicate that the columns on any sheet may have different lengths. Is this so?

**TripleC** · 02-26-2010, 01:16 AM

I am just using the LastRow UDF as an in-cell function on a sheet. I then use that value for the original code referenced above and for multiple other standard excel functions on the same sheet as well as other Report sheets.

The [A1] is a cell reference, or so I think. The UDF calculates correctly it seems. However, it does not always update or it errors out.

The function is needed as the sheet receives weekly or monthly data updates.

This is my first client project using VBA and I doubt that I can get it to the level of automation that I would like. At this point I am trying to append new data and copy cell formulas down. I'd eventually like to have the data scrubbing fairly automated too since many of the formulas identify rows with errors.

**protonLeah** · 02-26-2010, 04:41 PM

If I were you, I would replace all occurrences of the UDF with COUNT or COUNTA...

But it is just as easy to find the last row directly in the VBA procedures with:

Please Login or Register  to view this content.

Using a named range in VBA code

LinkBack

Thread Tools

Rate This Thread

Display

Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Re: Using a named range in VBA code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions