Calculations on Cell Values in VBA

[Blewyn]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Thanks

Blewyn