Hello everyone,

I'm quite new with VB; I've had experience with other programmin
languages though.

For this line:

Dim UniqueCount As Integer
UniqueCount = "=ROWS(R5C1:R" & LastRow2 & "C1)"

I know that I cannot automatically assign UniqueCount the value of tha
formula. How would I format the line such that I can indeed assig
UniqueCount the row value of that line? Thank you in advance

--
Obliviou
-----------------------------------------------------------------------
Oblivious's Profile: http://www.excelforum.com/member.php...fo&userid=3504
View this thread: http://www.excelforum.com/showthread.php?threadid=54787

Hey - welcome to the fun.

You can access most of the Excel worksheet formulae with
application.worksheetfunction. However, Rows isn't one of them. Fortunately,
VBA has its own equivalent.

UniqueCount = Range("A5:A" & LastRow2).Rows.Count

Jeff

"Oblivious" wrote
in message ...

Hello everyone,

I'm quite new with VB; I've had experience with other programming
languages though.

For this line:

Dim UniqueCount As Integer
UniqueCount = "=ROWS(R5C1:R" & LastRow2 & "C1)"

I know that I cannot automatically assign UniqueCount the value of that
formula. How would I format the line such that I can indeed assign
UniqueCount the row value of that line? Thank you in advance.


--
Oblivious
------------------------------------------------------------------------
Oblivious's Profile:
http://www.excelforum.com/member.php...o&userid=35041
View this thread: http://www.excelforum.com/showthread...hreadid=547877

You can't use Excel functions in VBA like that. VBA is a complete
programming language in it's own right. To do what you want you could use
the worksheetfuntion object. However, since you are using relative
referencing I can't see the ranges you want to put in there.

Jeff

"Oblivious" wrote
in message ...

Thanks for the prompt reply.

How about something like this then?




Dim UniqueCount As Integer

UniqueCount
=SUMPRODUCT(1/COUNTIF(R[3]C[-4]:R[296]C[-4],R[3]C[-4]:R[296]C[-4]))




Right now it is telling me it is a syntax error and that I am assigning
a wrong type. Thanks.


--
Oblivious
------------------------------------------------------------------------
Oblivious's Profile:
http://www.excelforum.com/member.php...o&userid=35041
View this thread: http://www.excelforum.com/showthread...hreadid=547877

Yes, I thought so. Could you please provide me with a sample coding of
how it would work using some arbitrarily chosen range? Perhaps
something like A1:A5 for example. Thank you.

Jeff Standen Wrote:
You can't use Excel functions in VBA like that. VBA is a complete
programming language in it's own right. To do what you want you could
use
the worksheetfuntion object. However, since you are using relative
referencing I can't see the ranges you want to put in there.

Jeff

"Oblivious"
wrote
in message
...

Thanks for the prompt reply.

How about something like this then?




Dim UniqueCount As Integer

UniqueCount
=SUMPRODUCT(1/COUNTIF(R[3]C[-4]:R[296]C[-4],R[3]C[-4]:R[296]C[-4]))




Right now it is telling me it is a syntax error and that I am
assigning
a wrong type. Thanks.


--
Oblivious

------------------------------------------------------------------------
Oblivious's Profile:
http://www.excelforum.com/member.php...o&userid=35041
View this thread:
http://www.excelforum.com/showthread...hreadid=547877



--
Oblivious
------------------------------------------------------------------------
Oblivious's Profile: http://www.excelforum.com/member.php...o&userid=35041
View this thread: http://www.excelforum.com/showthread...hreadid=547877