Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Defining Ranges

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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default Defining Ranges

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
-----Original Message-----
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.
.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Defining Ranges


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!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Defining Multiple "named" data ranges for Piot Tables in Excel 200 Fatih Can1968 Excel Discussion (Misc queries) 1 March 23rd 07 04:29 PM
Defining a name that represent identical ranges in every sheet Bernard Excel Discussion (Misc queries) 1 August 31st 06 02:18 PM
Defining identical names that represent different ranges of cells Bernard Excel Discussion (Misc queries) 2 August 31st 06 07:54 AM
Name Defining Chris Excel Discussion (Misc queries) 2 August 10th 06 03:06 PM
Defining Ranges Steve Excel Worksheet Functions 5 May 28th 05 07:41 AM


All times are GMT +1. The time now is 08:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"