count and sum functions

[bhaktany]

I am trying to use a count and sum functions in a VBA macro. I need to count a number of rows in worksheet1 and enter that value in worksheet 2. Same for the sum function.

Any ideas?

 

[AussieRob]

You will have to select the rows you want counted then:

?? = Selection.Rows.Count

 

[just_jon]

Use

Application.WorksheetFunction.Sum

and

Application.WorksheetFunction.Count

 

[bhaktany]

the problem being that the count and sum will be a variable. it won't be a constant number from time to time.

this function will be used to generate a part of a funding report that will change from one deal to the next.

sorry i am a beginner in VBA - how would i select the rows if it will be different each time? I will need to count the rows with data in column A until there is a blank in column A.

please help...

 

[punkin0601]

Did you ever find a solution to your problem? I am trying to do the same thing and can't figure out how to make it work.

Thank you!

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SumEm()
<SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
<SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
    [B1] = .Sum(Rng)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Puts the sum of column A into cell B1 of the active sheet.