Defining Ranges

[Greg Ghavalas]

In VBA I have two columns of data
Colour Number
Green 25
Green 12
Green 7
Blue 8
Blue 9 etc

I want to end up with the average number of Greens,
average number of Blues. I don't know how many of each I
have when I start. I can count them and determine where
green starts and ends. Therefore I can find the average.

My problem is I don't know how to define the start and
end of a variable range to the average function.

I used a cells(i,j)addressing method to find all the
greens and then blues.

I understand programming well, but am fairly new to
object programming. If this is a clear enough
explanation, I would most appreciate a hand.

 

[Hervé Hanuise]

Hi !

open a new module and copy this code in it :

Option Explicit

'Here is the path to follow:
'-find the reallastcells of the worksheet
'-define a range for the colour names
'-define a range for the numbers
'-use a sumprod formula to find out values per colours


Public reallastrow As Long
Public reallastcol As Long

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 9/07/2003 par Hervé Hanuise
'

'
'-find the reallastcells of the worksheet
ActiveWorkbook.Sheets("Sheet1").Select
GetReallastCells

'-define a range for the colour names
Range("A2:A" & reallastrow).Select
ActiveWorkbook.Names.Add Name:="nf_Colour", RefersTo:=
_
Selection

'-define a range for the numbers
Range("B2:B" & reallastrow).Select
ActiveWorkbook.Names.Add Name:="nf_Number", RefersTo:=
_
Selection
'reset pointer to cell A1
Range("A1").Select

'-use a sumprod formula to find out values per colours
'fix cell E1
Range("E1").Formula = "Searched Colour"
'fix Cell E2 with a dummy (actual Green)
Range("E2").Formula = "Green"
'localized french formula
'Range("F2").Formula = "=SOMMEPROD((nf_Colour=E2)
*nf_Number)"
'localized english formula
Range("F2").Formula = "=SUMPROD((nf_Colour=E2)
*nf_Number)"

End Sub

Sub GetReallastCells()
reallastrow = 0
reallastcol = 0

Range("A1").Select

On Error Resume Next

reallastrow = Cells.Find("*", Range("A1"),
xlFormulas, , xlByRows, xlPrevious).Row
reallastcol = Cells.Find("*", Range("A1"),
xlFormulas, , xlByColumns, xlPrevious).Column

Cells(reallastrow, reallastcol).Activate
'on return of this routine, reallastrow = last row
'reallastcol = last column

End Sub



It should work !!

regards, Hervé+
http://www.affordsol.be

 

[Greg Ghavalas]

Many thanks for your time. It's early morning in Australia at the
moment, I will try it at work today.

Regards


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!