Set variable to a function
 

I am using user forms in a workbook that I am doing. When I click a button, I want it to move to a cell that I have selected, using a variable, "Loc" in the offset. Under the objects, in ThisWorkbook I have this code...

Sub Variables()
Dim Loc
Loc = CountA("B:B")
End Sub

When this tries to run it gives the error message
"Compile Error:
Sub of Function not Defined"
and it highlights CountA

I think I got this to work before, but I dont know what I did to make it work, but I lost some data when the program froze, so I dont have that part of the code anymore.
Can this be done, or did I just imagine it happening before? If it can be done, what do I need to do to make it work?

Set variable to a function
 

Hi
try
Sub Variables()
Dim Loc
Loc = application.worksheetfunction.CountA(Range("B:B"))
msgbox Loc
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


Dan wrote:
I am using user forms in a workbook that I am doing. When I click a
button, I want it to move to a cell that I have selected, using a
variable, "Loc" in the offset. Under the objects, in ThisWorkbook I
have this code...

Sub Variables()
Dim Loc
Loc = CountA("B:B")
End Sub

When this tries to run it gives the error message
"Compile Error:
Sub of Function not Defined"
and it highlights CountA

I think I got this to work before, but I dont know what I did to make
it work, but I lost some data when the program froze, so I dont have
that part of the code anymore.
Can this be done, or did I just imagine it happening before? If it
can be done, what do I need to do to make it work?

Set variable to a function
 

Gosh you're fast. That worked great. Thanks a lot.

Set variable to a function
 

Sub Variables()
Dim Loc
Loc = Application.CountA(Range("B:B"))
End Sub

--
Regards,
Tom Ogilvy



"Dan" wrote in message
...
I am using user forms in a workbook that I am doing. When I click a
button, I want it to move to a cell that I have selected, using a variable,
"Loc" in the offset. Under the objects, in ThisWorkbook I have this code...

Sub Variables()
Dim Loc
Loc = CountA("B:B")
End Sub

When this tries to run it gives the error message
"Compile Error:
Sub of Function not Defined"
and it highlights CountA

I think I got this to work before, but I dont know what I did to make it
work, but I lost some data when the program froze, so I dont have that part
of the code anymore.
Can this be done, or did I just imagine it happening before? If it can be
done, what do I need to do to make it work?