excel VBA - search for cell values and place into array

[silentbuddha]

Hi, I am currently having some difficulties with this sunroutine that I created. My 2 dimensional array is empty.

- This code is within my UserForm1

- this sub is suppose to first select the WorkSheets("English"). The sheet contains col A, col B & col C. The sub will goto each row, if there is a cell value that matches the search criteria then all the cell values of that particular row is put into my 2-dimensional array.

- my while loop does not end. Is there something wrong with my code ?

Any ideas would be greatly appreciated !

--------------------------------------------------------------------------

Sub getResult(x, y, z)
Dim str1 As String
Dim str2 As String
Dim str3 As String

Dim cell1 As String
Dim cell2 As String
Dim cell3 As String

Dim tempStr1 As String
Dim tempStr2 As String
Dim tempStr3 As String

Dim counter As Integer

ThisWorkbook.Worksheets("English").Activate


'create 2 dimensional array -- 100 rows & 3 columns / note: lowerbound is 0
Dim myarray(100, 3)

str1 = x
str2 = y
str3 = z
'--------- Checking code ---------
MsgBox "str1 = " + str1 + " and str2 = " + str2 + " and str3 = " + str3


'---- looping structure of myarray -----
'the loop will check every row in Sheet1(English) and compare
'whether row 1 (col A, col B, col C) matches str1, str2 str3.
'If there is match to any str1 or str2 or str3 then the cell values will be read and placed into myarray()

counter = 0
cell1 = counter + 1
cell2 = counter + 1
cell3 = counter + 1

While Worksheets("English").Range("A" + cell1 + "") <> Empty And Worksheets("English").Range("B" + cell2 + "") <> Empty And Worksheets("English").Range("A" + cell1 + "") <> Empty


tempStr1 = Worksheets("English").Range("A" + cell1 + "").Value
tempStr2 = Worksheets("English").Range("B" + cell2 + "").Value
tempStr3 = Worksheets("English").Range("C" + cell3 + "").Value

'--------- Checking code ---------
MsgBox "tempStr1 = " + tempStr1 + " and tempStr2 = " + tempStr2 + " and tempStr3 = " + tempStr3


If tempStr1 = str1 Or tempStr3 = str2 Or tempStr3 = str3 Then

myarray(counter, 0) = tempStr1
myarray(counter, 1) = tempStr2
myarray(counter, 2) = tempStr3

counter = counter + 1

End If

tempStr1 = vbNullString
tempStr2 = vbNullString
tempStr3 = vbNullString

Wend

Call showResult(myarray)

End Sub

[kadghar]

Hi, I am currently having some difficulties with this sunroutine that I created. My 2 dimensional array is empty.
...

i didnt read your full question (sorry) but it seems to me that you're having troubles into reading your excel's cells into an array.
well, the easiest way is to use a Variant.

when you asign a Range to a variant, the variant doesnt change to a Variant/Range, but to a Variant/Array (that's so cool)

something like this will do

dim a 
a = range(cells(1,1), cells(5,5))

for example.

or if you want to do it with some specific cells, something like this will do

dim Arr1(1 to 10) as String
arr1(1) = cells(1,1)
arr1(2) = cells(3.1)
'and so on
 
'or you can make them from different worksheets:
 
arr1(3) = worksheets("sheet1").cells(1,1)

HTH