Hi Gurus,
May I ask if is it possible to search in a two dimensional array in memory in MS Excel 2007?
What I mean is that my array table values were searched cells which are scattered in the Excel sheet (just one worksheet). Or I need to print the array table then VLOOKUP on it?
To elaborate, the main functions should be:
1. VBA searches for the Company Code and Company Description (Columns B and C) - I've succcessfully captured the two dimensional arrays but can it be improved?
NOTE: The number of Company Codes is dynamic
2. Search the matching variable Company Number among the searched Company Number and Company Description array table. Company Number will be the search value and the output should be the Company Code Description.
Hope anyone could help,
- bye
May I ask if is it possible to search in a two dimensional array in memory in MS Excel 2007?
What I mean is that my array table values were searched cells which are scattered in the Excel sheet (just one worksheet). Or I need to print the array table then VLOOKUP on it?
To elaborate, the main functions should be:
1. VBA searches for the Company Code and Company Description (Columns B and C) - I've succcessfully captured the two dimensional arrays but can it be improved?
NOTE: The number of Company Codes is dynamic
Code:
Dim strHeadCoCode As String, strHeadCoDesc As String 'Two variables for Company Code and Description
Dim vCompany As Variant, Sws As Worksheet
Dim lngArrCount As Long, lngRw As Long, intCount As Integer
ReDim vCompany(1 To lngRw, 1 To 2)
'Array for Company Code and Company Code Description
For lngArrCount = 1 To lngRw
With Sws
If .Cells(lngArrCount, "A").Value = "Parent:" And Len(.Cells(lngArrCount, "B")) = 4 Then 'Condition to get the Company Code and Description
'Table Array
vCompany(intCount, 1) = .Cells(lngArrCount, "B").Value
vCompany(intCount, 2) = .Cells(lngArrCount, "C").Value
Debug.Print (vCompany(intCount, 1)) 'Test if all Company Codes were captured
Debug.Print (vCompany(intCount, 2)) 'Test if all Company Code Descriptions were captured
intCount = intCount + 1
lngArrCount = lngArrCount + 1
End If
End With
Next2. Search the matching variable Company Number among the searched Company Number and Company Description array table. Company Number will be the search value and the output should be the Company Code Description.
Hope anyone could help,
- bye
Last edited:
