Want to use standard excel functions in vb
 

I have a worksheet containing a list of items that ore of interest to me.
Periodically , I want to add to the list or modify one of the items in the
list.
Each item has an item ID in Col 1 and 'many' other bits of info (text,
numeric, dates) in subsequent columns

I built a user form in VB to pull info into the form from the indicated row
of the db and put it back into the DB as an indicated row.

In order to set the item ID of a new item, I want to get the maxvalue for
all current items.

I wanted to use the following code:

InitID.text = max(Worksheets("OppDB").cells(a1:a65535))

Where InitID is the name of the textbox in my form containing the Initiative
ID, and OppDB is the worksheet within the workbook that holds all data on the
set of initiatives I'm tracking.

I suppose I could do this in a loop, but, since excel has a function that
returns the max value found in a range (a1:aN) I thought this would work.
There are many other excel functions that I might want to use within VB --
what am I missing about how to do this (I know - the answer is probably
obvious).

TIA
Mark

Want to use standard excel functions in vb
 

Mark,

Please try:

Application.WorkbookFunction.Max(...)

Bill

"Mark" wrote in message
...
I have a worksheet containing a list of items that ore of interest to me.
Periodically , I want to add to the list or modify one of the items in the
list.
Each item has an item ID in Col 1 and 'many' other bits of info (text,
numeric, dates) in subsequent columns

I built a user form in VB to pull info into the form from the indicated
row
of the db and put it back into the DB as an indicated row.

In order to set the item ID of a new item, I want to get the maxvalue for
all current items.

I wanted to use the following code:

InitID.text = max(Worksheets("OppDB").cells(a1:a65535))

Where InitID is the name of the textbox in my form containing the
Initiative
ID, and OppDB is the worksheet within the workbook that holds all data on
the
set of initiatives I'm tracking.

I suppose I could do this in a loop, but, since excel has a function that
returns the max value found in a range (a1:aN) I thought this would work.
There are many other excel functions that I might want to use within VB --
what am I missing about how to do this (I know - the answer is probably
obvious).

TIA
Mark

Want to use standard excel functions in vb
 

Thanks, Bill.

Here's the statement I used:

InitID.Text =
Application.WorkbookFunction.Max(Worksheets("OppDB ").Cells(a1:a200))

I'm (still) getting a compile error ("Expected list separator or )" )with
the ":" highlited. Probably a bone head error :-).

"Bill James" wrote:

Mark,

Please try:

Application.WorkbookFunction.Max(...)

Bill

"Mark" wrote in message
...
I have a worksheet containing a list of items that ore of interest to me.
Periodically , I want to add to the list or modify one of the items in the
list.
Each item has an item ID in Col 1 and 'many' other bits of info (text,
numeric, dates) in subsequent columns

I built a user form in VB to pull info into the form from the indicated
row
of the db and put it back into the DB as an indicated row.

In order to set the item ID of a new item, I want to get the maxvalue for
all current items.

I wanted to use the following code:

InitID.text = max(Worksheets("OppDB").cells(a1:a65535))

Where InitID is the name of the textbox in my form containing the
Initiative
ID, and OppDB is the worksheet within the workbook that holds all data on
the
set of initiatives I'm tracking.

I suppose I could do this in a loop, but, since excel has a function that
returns the max value found in a range (a1:aN) I thought this would work.
There are many other excel functions that I might want to use within VB --
what am I missing about how to do this (I know - the answer is probably
obvious).

TIA
Mark

Want to use standard excel functions in vb
 

Dim myRange As Range
Set myRange = Worksheets("OppDB").Range("A1:A200")
InitID.Text = Application.WorksheetFunction.Max(myRange)

Do the WorksheetFunction on a variable
Mike F
"Mark" wrote in message
...
Thanks, Bill.

Here's the statement I used:

InitID.Text =
Application.WorkbookFunction.Max(Worksheets("OppDB ").Cells(a1:a200))

I'm (still) getting a compile error ("Expected list separator or )" )with
the ":" highlited. Probably a bone head error :-).

"Bill James" wrote:

Mark,

Please try:

Application.WorkbookFunction.Max(...)

Bill

"Mark" wrote in message
...
I have a worksheet containing a list of items that ore of interest to
me.
Periodically , I want to add to the list or modify one of the items in
the
list.
Each item has an item ID in Col 1 and 'many' other bits of info (text,
numeric, dates) in subsequent columns

I built a user form in VB to pull info into the form from the indicated
row
of the db and put it back into the DB as an indicated row.

In order to set the item ID of a new item, I want to get the maxvalue
for
all current items.

I wanted to use the following code:

InitID.text = max(Worksheets("OppDB").cells(a1:a65535))

Where InitID is the name of the textbox in my form containing the
Initiative
ID, and OppDB is the worksheet within the workbook that holds all data
on
the
set of initiatives I'm tracking.

I suppose I could do this in a loop, but, since excel has a function
that
returns the max value found in a range (a1:aN) I thought this would
work.
There are many other excel functions that I might want to use within
VB --
what am I missing about how to do this (I know - the answer is
probably
obvious).

TIA
Mark