I'm trying to create a formula to increment a counter. The variable name
contains a variable.
sCt = "A"


If rCell = "16" Then
sCt = rCell.Offset(, 2).Value
"l16" & sCt & "ct" = "l16 " & sCt & "ct" + 1
End If

It's turning red on me. I want it to yield

l16ACt = l16ACt + 1

That is "el sixteen Ay cee tee"

Does anyone know how to enter this?

Thanks!

VB doesn't support what you are trying to do, at least not in the way you
are trying to do it. You should read up on arrays... they were placed in the
VB language strictly to be able to handle what you are attempting to do.
Basically, arrays are identically named variables that use an index value to
differentiate between the individual members of the array. This seems like a
reasonably decent introduction to arrays for you to study up on them...

http://patorjk.com/programming/tutorials/vbarrays.htm

If you Google "vb arrays tutorial" (without the quote marks), you will get
other links as well.

--
Rick (MVP - Excel)


"salgud" wrote in message
...
I'm trying to create a formula to increment a counter. The variable name
contains a variable.
sCt = "A"


If rCell = "16" Then
sCt = rCell.Offset(, 2).Value
"l16" & sCt & "ct" = "l16 " & sCt & "ct" + 1
End If

It's turning red on me. I want it to yield

l16ACt = l16ACt + 1

That is "el sixteen Ay cee tee"

Does anyone know how to enter this?

Thanks!

On Fri, 25 Sep 2009 13:13:27 -0400, Rick Rothstein wrote:

VB doesn't support what you are trying to do, at least not in the way you
are trying to do it. You should read up on arrays... they were placed in the
VB language strictly to be able to handle what you are attempting to do.
Basically, arrays are identically named variables that use an index value to
differentiate between the individual members of the array. This seems like a
reasonably decent introduction to arrays for you to study up on them...

http://patorjk.com/programming/tutorials/vbarrays.htm

If you Google "vb arrays tutorial" (without the quote marks), you will get
other links as well.

Darn! I've been avoiding learning about arrays for some time. Now you're
making me learn. I hate when that happens! :)

Seriously, thanks for the lead. This gives me the incentive to learn about
arrays (again, used them a little bit years ago) and get over the hump. My
situation appears to be a good learning example of how/when to use arrays.

I did find a workaround for my original problem, but it only works if I'm
willing to temporarily abandon my standard variable naming convention
(which I'd prefer not to do). If I change the variables to one character,
like "R", "A", "G" and "B", I can use those in Don's formula to get my
desired counts of those categories. E.g.,

If rCell = "16" Then
sCt = rCell.Offset(, 2).Value
sCt = sCt + 1
End If

Works fine if sCt is the code and the variable name both and is one of R,
A, G or B.

But it would probably do me good to learn arrays, so thanks for the
suggestion. Now I need to go and figure out how to do this with an array...