using arrays in loops.....maybee :)

[steve_bris]

Hi.

I have written a lot of pages of code and I have hardcodded all the variable names in.....now I have to change it to use outputs from the database which I have printed into a column in excel.
I think this can be done using arrays but I don't know much about them

Say the output that I have got from my database in column J is Fred....john....ben... in rows 1 and 2 and 3.

Now I have to search throught Column A and when I find the words Fred, john or ben( the ones in column J) i have to impliment some code.

I previuously hardcoded the names in it like this

introw = 0
Do
intRow = intRow + 1
Range("A" & intRow).Select
If Selection = "Fred" Then
' do stuff
Elseif Selection = "john" Then
' do stuff
Elseif Selection = "ben" Then
' do stuff
Endif
Loop until intRow = 20

How could I use arrays or some other type of code to write the loop to search if the selection is in column J.
for example i would end up with a lot more concise code like this

introw = 0
Do
intRow = intRow + 1
Range("A" & intRow).Select
If Selection = "value from array" Then
' do stuff

Endif
Loop until intRow = 20

I hope I have made what I am asking clear....I doubt it.....hehe

Any help would be greatly appreciated.

 

[WayneRyan]

Steve,

You have 20 rows exported from Access to Excel.

Each row has a name in Column A.

Each row has a name in Column B.

If there is any correlation between the names in Column A & the ones in Column B,
can't it be done in Access prior to the export?

Regardless, you can use a DLookUp function call to the query that fed Excel. If
the current "Column A" value is in the "Column J" field, then do whatever.

Just thought I'd respond. I really don't see why you'd transfer data to Excel and
then manipulate it.

Wayne

 

[steve_bris]

Thanks Wayne....but I am only allowed to manipulate the excel file that I am given.....so is there any way to do it apart from in the access database ?

 

[DALeffler]

Are you trying to match names in different columns (column 1 and column 10) of the same row of the worksheet? Try:

Sub TstMacro()

Dim I As Integer

With Workbooks("MyWorkBook").WorkSheets("MyWorkSheet")
    For I = 1 To 20
        If .Cells(I, 1) = .Cells(I, 10) Then
             'Do stuff...
        End If
    Next I
End With

Exit Sub

Be aware that in Excel: john <> John...

hth,

 

[Sergeant]

You can use an array to hold your control list (column J). Arrays are funny animals, and I would advise you to read the help entries for them and play with them until you see their advantages and limitations.
There may be faster coding for this, but here's what I came up with...

At the top of the module (ensures your array index starts with 0):

Option Base 0

In your sub:

    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

    y = 0
    Do
        For i = 0 To UBound(aryNames)
            If Range("a1").Offset(y, 0) = aryNames(i) Then
                'highlight names that match...
                Range("a1").Offset(y, 0).Interior.Color = vbYellow
            End If
        Next i
        y = y + 1
    Loop While Not Len(Range("a1").Offset(y, 0)) = 0

This assumes that you don't have a header row (as in your examples thus far). If you use one, you can simply change the option base to 1, change x = 0 to x = 1, and y = 0 to y = 1. (I think that covers it, you'd have to try it)
Notice that you don't have to know how many entries are in either column, as long as they are contiguous lists (no blanks in the middle).
If you have any questions about what's going on with the array above, please post them.

 

[steve_bris]

Thanks Sergeant...I have been reading up a little on arrays....and they are pretty complicated

i will give your code a go though and see if i can get it to work.

Thanks for being such a great help, I only have 2 weeks till the code is due in and I am getting SOOOOOOOOO much good information from the board

I have figured out a way to do the same thing as the arrays with a loop, if i can get the contents of the following links question to work :0)

http://www.access-programmers.co.uk/forums/showthread.php?t=84317

Thanks heaps
Steve