PDA

View Full Version : Macro to provide count of results



asad.naqvi
07-13-2010, 06:42 AM
Hi,
I have a function in the format of an excel add in that when executed on an excel spreadsheet gives me a set of results.
Now i need to write a macro using VBA that would call the function and invoke it and then provide the count of the no of results that the function would display when invoked. Can anyone help me out with this? pls its very urgent.

Thanks

GTO
07-13-2010, 07:26 AM
It might help if you posted the function and say how you want it called (a button, worksheet event, etc).

asad.naqvi
07-15-2010, 04:54 AM
hey,
thanks a lot for ur help. i wrote this code where i input a function from the user that displays the a set of results. So now since i want the count of the no of results displayed iam executing the function and then once it shows me the resutls am checkin to see if the cells have a text result attached to it. But then there seems to be some bug with this code since it does not show me the correct results the first time i run the macro and then when i run it for the 2nd time it displays the correct results.
Does anyone know what is going wrong with this code.

Private Sub macrostringe()
Dim letters As String
Dim counter, rangesheet
rangesheet = 1
counter = 0
letters = InputBox("enter your letters with commas in the middle")
Cells(1, 1).Value = letters
Sheets("Sheet1").Select
Range("A1").Select

Do Until Selection.Cells(rangesheet, 1).Value = ""

counter = counter + 1
rangesheet = rangesheet + 1
'MsgBox counter
Loop
MsgBox counter
Range("B1").Select
Cells(1, 3).Value = rangesheet - 1
End Sub

GTO
07-15-2010, 05:19 AM
I am afraid I am not 'getting' what it is you are trying to do.

As I read your code, cell A1 is populated with whatever was typed into the InputBox and this cell is selected.

Then we use a .cells reference that is relative to selection, which was A1. The second time thru the loop, we are looking at A2, so the loop exits from the top.

In plain language, what are you wanting the code to do?

Mark

asad.naqvi
07-16-2010, 05:38 AM
hi,
see i work on a financial product that has a excel interface. the software accepts functions from a user such as displayin closing price, market capitalization etc etc for public listed companies around the world. on entering the functions in a cell the appropiate values are displayes.
So as an example the user can enter a funtion to list out the close price of microsoft for the last 10 days n he would get 10 different values corressponding to the last 10 close prices.
Now my macro is supposed to ask the user to enter the function and then on clickin ok in the input box after entering the function the various values (lets say in this case it is close price for the microsoft for the last working week so that means there should be 5 values corresspoding to the 5 diffn days) will be displayed on the excel sheet vertically.
So now i my macro is supposed to count the no of values or results that have been displayed on the sheet and then give the count on cell (1,3).
But what is happening is that the macro displays the correct set of values only when compiled the 2nd time.
So there seems to be a bug somewhere that is causing the error.