Calculations on Cell Values in VBA
 

I'd like to perform some calculations on cell values in VBA, for exampl
:

price temperature
10 100
20 150
30 200

What I'd like to do is sum the prices and average the temperatures, an
enter the results in a given cell, using variables as cell reference
(I don't know the absolute row reference for the data columns).

Here's the line of code I have so far :

Cells(Count, 1) = WorksheetFunction.Sum(Range("a"
TopRowOfCurrentTable & ":a" & (Count - 1)))

Where 'Count' is a loop variable that identifies the row below the las
row in a given table and 'TopRowOfCurrentTable' identifies the top row
When I run this code I get

Error 1004
Method 'Range' of object '_Global' failed

Any idea why it's not working ? I know I could build a loop to simpl
add all the values using a variable and then enter it into the cell
but I thought that using a worksheet function like this would be mor
efficient.

Thanks,

Blewy

--
Message posted from http://www.ExcelForum.com

Calculations on Cell Values in VBA
 

Hi Blewyn,

I have just tried it and it works for me.

Add these lines into the code before the line that errors and see what it
outputs

Debug.Print Activesheet.Name
Debug.Print Count
Debug.Print TopRowOfCurrentTable

This will output values into the immediate window.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Blewyn " wrote in message
...
I'd like to perform some calculations on cell values in VBA, for example
:

price temperature
10 100
20 150
30 200

What I'd like to do is sum the prices and average the temperatures, and
enter the results in a given cell, using variables as cell references
(I don't know the absolute row reference for the data columns).

Here's the line of code I have so far :

Cells(Count, 1) = WorksheetFunction.Sum(Range("a" &
TopRowOfCurrentTable & ":a" & (Count - 1)))

Where 'Count' is a loop variable that identifies the row below the last
row in a given table and 'TopRowOfCurrentTable' identifies the top row.
When I run this code I get

Error 1004
Method 'Range' of object '_Global' failed

Any idea why it's not working ? I know I could build a loop to simply
add all the values using a variable and then enter it into the cell,
but I thought that using a worksheet function like this would be more
efficient.

Thanks,

Blewyn


---
Message posted from http://www.ExcelForum.com/

Calculations on Cell Values in VBA
 

Thanks - I think I know where I was going wrong, and the answer to th
follwing could solve the problem for me. (also, I need the actua
formula in the cell not just the result). This little routine return
the correct answer 4 -

MyString = Str(Sqr(16))
MsgBox (MyString)

However this routine gives me a type mismatch error -

MyString = Str(Range(Cells(1, 2), Cells(2, 3)))
MsgBox (MyString)

What I want in MyString is "b1:c2"

Any idea how I can do this ?

Thanks,

Blewy

--
Message posted from http://www.ExcelForum.com

Calculations on Cell Values in VBA
 

MyString = Range(Cells(1, 2), Cells(2, 3)).Address(false,false)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Blewyn " wrote in message
...
Thanks - I think I know where I was going wrong, and the answer to the
follwing could solve the problem for me. (also, I need the actual
formula in the cell not just the result). This little routine returns
the correct answer 4 -

MyString = Str(Sqr(16))
MsgBox (MyString)

However this routine gives me a type mismatch error -

MyString = Str(Range(Cells(1, 2), Cells(2, 3)))
MsgBox (MyString)

What I want in MyString is "b1:c2"

Any idea how I can do this ?

Thanks,

Blewyn


---
Message posted from http://www.ExcelForum.com/

Calculations on Cell Values in VBA
 

:) :) :) :) :) :) :) :) :) :)

Thanks

Blewyn


---
Message posted from http://www.ExcelForum.com/