VBA finding text and assigning variable

[cmefly]

Hi,

For the life of me i can't figure this one out...and it seems easy..(or so i think)...blah!

using VBA - i'm trying to find every cell in column B that is equal to "AAA". Then, i'd like to assign a variable its location (for example, if B9 = "AAA", then variable A = 9, if the next time it finds "AAA" is in cell B400 then variable B = 400....etc in that order). The maximum number of "AAA" that can be found is 25.

is this possible? I'm having problems looping through the alphabet....

thanks,

Marc

 

[prajul89]

Selection.Formula = "=UPPER(hidden1!A" & j & ")[COLOR=Red]&[/COLOR] is great"

See if this can help!!!

 

[cmefly]

nope...

does it have anything to do with the store variable being declared as long....

??

 

[prajul89]

This was the line you where getting an error right?
And which variable are you talking about "J"?

 

[cmefly]

yes, i get a runtime error 1004 on that line....yes..variable J

 

[cmefly]

The source of my error is this:

Selection.Formula = "=UPPER(hidden1!A" & j & ")" & " is great"


when i do this...

Selection.Formula = "=UPPER(hidden1!A" & j & ")"

everything works fine....


what gives???

 

[prajul89]

I don't know what is the problem. that line is working fine with me.
as far as you loop is concerned the loop for J should be 1 to count -1.

 

[prajul89]

Selection.Formula = "=UPPER(hidden1!A" & j & ")& is great"

so you never used what i had given to you?
Please copy paste this Code

 

[cmefly]

my bad

sorry and thank you for your help...i tend to close of my quotes before putting an & sign...but i finally understand what you were trying to do...