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

 

[cmefly]

BUMP....could really use some help with this

 

[kpark91]

Hi, cmefly.
What have you tried?
Can we see the code?

 

[kpark91]

Just to get you started, if you have the same idea as me
your code should look like

Sub Test
    Dim LR As Long, i As Long
    LR = Range("B" & Rows.count).End(xlUp).Row
 
    For i = 1 To LR
         If Range("B" & i).Value = "AAA" Then
             'Store position of the "AAA" occurrence
         End If
    Next i 
End Sub

 

[cmefly]

kpark...thank you but that's what i have...i can find the cell...but i'm uncertain how to record the location of each find as a different variable.....

suggestions?

 

[prajul89]

Use Array

Dim store(25)

And then store it as store(i) as your variable for 25 time

 

[cmefly]

thanks!!

i just found this online that basically takes what you mentioned...check it out...

Sub TestStaticArray()
' stores 10 names in the workbook in the array variable MyNames()
Dim MyNames(1 to 10) As String ' declares a static array variable
Dim iCount As Integer
For iCount = 1 To 10
MyNames(iCount) = ThisWorkbook.Names(iCount)
Next iCount
Erase MyNames() ' deletes the varible contents, free some memory
End Sub

 

[kpark91]

Use Array

Dim store(25)

And then store it as store(i) as your variable for 25 time

Exactly except make sure you're dimming with lowerbound in mind as well.
As excel can be annoying with lowerbound base if you don't specify it

Dim store(0 To 24)
'or
Dim store(1 To 25)

Then you could use count variable to store these variables.

Might as well give you the whole code for better understanding

Sub Test
    Dim LR As Long, i As Long, store(0 To 24) As Long, count As Integer
    LR = Range("B" & Rows.count).End(xlUp).Row
    count = 0
    For i = 1 To LR
         If Range("B" & i).Value = "AAA" Then
             store(count) = Range("B" & i).Row
             count = count + 1
         End If
    Next i 
End Sub

 

[cmefly]

kpark...thank you very much!!!!!!

 

[cmefly]

hi..my macro isn't working and i can't figure out why...can some please run this mini macro and help me ? (the values in the hidden1 worksheet are strings..names of people)

my error is at the selection.formula part.......

Sub replacenames()

Dim LR As Long, i As Long, store(0 To 24) As Long, count As Integer
Dim j As Integer

LR = Range("B" & Rows.count).End(xlUp).Row - 1
count = 1
For i = 1 To LR
If Range("B" & i).Value = "AAA" Then
store(count) = Range("B" & i).Row
count = count + 1
End If
Next i


For j = 1 To count
Range("B" & store(j)).Select
Selection.Formula = "=UPPER('hidden1'!A" & j & ") is great"
Next j

End Sub