concatenation as variable name

[steve_bris]

Hi.

I would like to assign the value of a column of cells to a variable with a different number on the end as it moves down the rows.

Dim No as integer
No = 1
Sheets("Sheet1").Select
Range("J1").Select
Do

"ROLE" & No = Activecell.value <--------------- This is the line that gives me an error
ActiveCell.offset(rowOffset:=1, columnOffset:=0).Activate
No = No + 1​

Loop Until ActiveCell = ""

So if cell J1 has John in it, then ROLE1 = "John"..........and cell J2 has me, then ROLE2 = "Me"

Thanks for any help
Steve

 

[Sergeant]

Hi Steve,
Constructive comments:
1. Don't use "No" as a variable name. (Use something like "intNo" or "intSeq")
2. If you remove the quotes from "ROLES", it might work. (I didn't try it)
3. Remember how I said that you don't need to "select" things before performing actions on those things in code? Also applies to "activate"ing things.
4. What you are attempting here is exactly why you might use an array.

 

[steve_bris]

ok....thanks sargeant.........unless you haven't guessed...I am not a programmer, I am a graduate engineer and i have been asked to just help on this project until it finishes in 2 weeks........hence my dodgy coding ideas.....hehe

So would you be able to help me with some code to just get everything from column J into an array.

Then I can use this array to search through lots of other areas in my workbook

Your really really helping me out here

Thanks

 

[Sergeant]

So would you be able to help me with some code to just get everything from column J into an array.
Then I can use this array to search through lots of other areas in my workbook

I already did. It's this part...

Dim aryNames() As String
    x = 0
    Do
        ReDim Preserve aryNames(x)
        aryNames(x) = Range("j1").Offset(x, 0)
        x = x + 1
    Loop While Not Len(Range("j1").Offset(x, 0)) = 0

...of the code that I posted here.

 

[Sergeant]

I just caught this...

Then I can use this array to search through lots of other areas in my workbook

Move the line

Dim aryNames() As String

to the top of a module as

Public aryNames() As String

and then you can make a Sub to fill it.

Public Sub FillArray()
    x = 0
    Do
        ReDim Preserve aryNames(x)
        aryNames(x) = Range("j1").Offset(x, 0)
        x = x + 1
    Loop While Not Len(Range("j1").Offset(x, 0)) = 0
End Sub

I think that will work.