Variable in function code

[sachin parab]

Hi experts,

I want to used counta function in VBA, where workbook and worksheet will change.
I would like to use variable in formula and answer should be store in another variable.

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

can anyone guide me how to use variable in function and answer will store directly in another variable.
Thanks in advance

[Richard Buttrey]

Hi,

Untested but something like

Please Login or Register  to view this content.

[TMS]

Or this:

Please Login or Register  to view this content.

Regards, TMS

[sachin parab]

Dear TMS,

Thanks for your quick response, but which used first code i.e.

Please Login or Register  to view this content.

it gives Compile error "Expected list separate or )",

and in second code i.e.

Please Login or Register  to view this content.

it give error Run-time error '9' subscript out of range

so can you explain where I did wrong.
Thanks for your help

[TMS]

Did you change the workbook name and worksheet name? Is the workbook open? Does the sheet exist.

The first code was yours to show what I was trying to match.

Regards, TMS

[sachin parab]

TMS thanks.
It got resolved, thanks again for your support.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.