Excel ranges - vba newbie 1

[sweetleaf]

Hello,

I'm a vba newbie,

I have a spreadsheet where in one range everything is grouped by Dealer then Region. There are line items within each region and at the end of each region is a total value of sales, tax etc for each region.
One empty cell divides each range.

I want to put a command button on the spreadsheet which will loop through and read each of the region totals into variables (and later pass them to a dll).

Does anyone know how to trap these values in a spreadsheet where although the columns are fixed and never change in position the rows do?

Any help would be soooo totally appreciated!

 

[kevinclark]

I am sure there are other ways but if you want to try your hand at a Dynamic Array and Recursive Function then this may be of interest to you.

'Create a temporary array to hold the values
Private tmpArray() As Integer

'This is the routine that initiates it all
Sub StartNow()
Dim i As Integer
'Call the function sending it the row to start at
'and the first array index of zero
getTotal 1, 0
For i = 0 To UBound(tmpArray)
MsgBox tmpArray(i)
Next i
End Sub


'This recursive funtion will on itself until it finds an
'instance of one blank cell following another thus
'indicating the end of the search. This is assuming that
'each region is ALWAYS seperated by 1 row and 1 row only.

Function getTotal(r As Long, Num As Integer)
'Note: the 3 is the 3rd column - change as required
If Sheets("Sheet1&quot.Cells(r, 3).Value = "" Then Exit Function
Do While Sheets(&quot;Sheet1&quot.Cells(r, 3).Value <> &quot;&quot;
r = r + 1
Loop
ReDim Preserve tmpArray(Num)
tmpArray(Num) = Sheets(&quot;Sheet1&quot.Cells(r - 1, 3).Value
getTotal r + 1, Num + 1
End Function

 

[DaleWatson123321]

Hi sweetleaf,

Another option...

Probably THE most powerful, but often overlooked, component of Excel is its &quot;database functions&quot;.

Based on your description, it appears obvious that these database functions are indeed the best solution.

By using separate database formulas for each region, you can &quot;instantly&quot; have your region totals - WITHOUT having to use a VBA routine. This option is MUCH faster because it provides &quot;instant&quot; results.

If you have explored the database functionality of Excel, you might have run into difficulty because of a couple of FALSE ERROR messages and quirks regarding setting up the criteria for the formulas.

However, I can assure you that the time spent in learning how to work around these quirks, will we well worht the investment in your time.

If you would like, I can help. My recommendation would be for you to send your file, or a scaled down version. If your data is sensitive, replace it with fictitious data but still have the fictitious data reflect the nature of the type of data you're working with.

I'll then create a matrix of these database formulas and return the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S. I should have included my HOME address, in case you want to reach me on the weekend.

HOME: nd.watson@shaw.ca

 

[sweetleaf]

Hey Dale,

Thanks so much for sharing your vast knowledge of excel database functions - what an eye-opener! The techniques you have shown me leave me in awe - you're right there is very little recognition for a set of tools so useful and value-added - it's baffling!

I'd highly recommend the use of excel db functions wherever the need arises to capture and process data locally or remotely (ie. from excel to a dll etc).

Thanks for taking out the time to show me - that was true class!

cheers Dale,
Mark