How can I build a Range, use it and remember it in a variable for later in a function?

**Geek4eye** · 07-07-2012, 02:42 PM

Hi!

Here is my dilemma: I have an old set of VBA functions which I wrote that process a vendor pricing sheet from their config tool. I'm tired of manually entering in AutoSUMs at the bottom of each category (usually 4 but sometimes only 2 or 3) and then manually linking them to a summary at the top. Each of the up to four categories can have a variable number of lines, so the locations of the desired SUM cells also varies.

I've found the proper place in the VBA funtion to add the code, but I can't figure out how to set the Ranges. If I try to use a variable to grab the top range for the SUM function, then I get a 'range' of object'_global' error. If I try to calculate it based on a Count variable used for the line by line processing, then I get an 'Application-defined or object-defined' error.

This is probably stupid simple, but I can't seem to view it properly...

[Uh, obviously I'm new here - how do you get that inside window with the code in it? I'll just attach a text file sample (reduced significantly, removing most of the existing code that processes each product line)...]

Thanks!

**Geek4eye** · 07-08-2012, 01:15 PM

Thanks to:
kvsrinivasamurthy
who posted in another thread titled:
Autosum with VBA

With that example as a starting point, I was able to build a Function to do the job. It turned out to be easier to reprocess the sheet rather than do it in line with the previous Function. This method uses string manipulation to create the Range values.

Here is the code, which should be easier to read than kvsrinivasamurthy's, and is more general purpose for my use.

Formula:

Please Login or Register  to view this content.

**Geek4eye** · 07-08-2012, 01:49 PM

Oops, I forgot to link the SUM cell back up to the top of the sheet. Given that, since each category subtotal goes to a different place up top, it makes better sense to abandon the big OR statement and break them out into separate sets of code. Here is the first one:

Formula:

Please Login or Register  to view this content.

How can I build a Range, use it and remember it in a variable for later in a function?

LinkBack

Thread Tools

Rate This Thread

Display

How can I build a Range, use it and remember it in a variable for later in a function?

Re: How can I build a Range, use it and remember it in a variable for later in a function?

Re: How can I build a Range, use it and remember it in a variable for later in a function?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions