ExcelBanter - Adding values to 2-dim array

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Adding values to 2-dim array (https://www.excelbanter.com/excel-programming/317860-adding-values-2-dim-array.html)

Adding values to 2-dim array

Okay, I'm going to try something new, so everybody duck!!

I want to have an array that already has one dimension
filled in, then have the second dimension filled in at
run time. So if the first dimension is:

Earl
Larry
Fred
Janet
Carrie

I want to add values based on the name. The values will
not always come in the same order as the names. Is there
a way that I can "search" the array for the name and then
add the value next to the match?

tod

Adding values to 2-dim array

This may give you an idea:

Option Explicit
Sub testme04()

Dim myArray(1 To 5, 1 To 2) As Variant
Dim myVal As String
Dim res As Variant

myArray(1, 1) = "Earl"
myArray(2, 1) = "Larry"
myArray(3, 1) = "Fred"
myArray(4, 1) = "Janet"
myArray(5, 1) = "Carrie"

myVal = "Larry"

With Application
res = .Match(myVal, .Index(myArray, 0, 1), 0)
If IsError(res) Then
MsgBox "Not Found"
Else
myArray(res, 2) = "found it"
End If
End With

End Sub

Tod wrote:

Okay, I'm going to try something new, so everybody duck!!

I want to have an array that already has one dimension
filled in, then have the second dimension filled in at
run time. So if the first dimension is:

Earl
Larry
Fred
Janet
Carrie

I want to add values based on the name. The values will
not always come in the same order as the names. Is there
a way that I can "search" the array for the name and then
add the value next to the match?

tod

--

Dave Peterson

Adding values to 2-dim array

Tod wrote:
Okay, I'm going to try something new, so everybody duck!!

I want to have an array that already has one dimension
filled in, then have the second dimension filled in at
run time. So if the first dimension is:

Earl
Larry
Fred
Janet
Carrie

I want to add values based on the name. The values will
not always come in the same order as the names. Is there
a way that I can "search" the array for the name and then
add the value next to the match?

tod

This sounds like an interesting problem, but:

1. How do you have an array with the first dimension filled *other than*
at runtime? I.e., don't *both* dimensions have to be loaded at runtime?

2. From where are the corresponding values coming, and how are they
associated with the names?

Alan Beban

Adding values to 2-dim array

The excel.worksheet function MATCH(Person,{NameList},0) will return the
index number of the name supplied:
e.g. Janet would return 4

Example:
In Excel
- Name the worksheet "theName"
- Name the list of names "NameList"
- Assume you want ages placed one col to right of names

Using this from VBA

Sub Test()
FillList "Fred", 42
FillList "Janet", 38
FillList "Brian", 22
End Sub

Sub FillList(strName, lngAge)
Const BAD_MATCH As String = "Unable to get the Match"
Const EXACT_MATCH As Long = 0
Dim mySheet As Excel.Worksheet
Dim myRange As Excel.Range
Dim ListIndex As long
Set mySheet = Excel.ActiveWorkbook.Worksheets("theSheet")
Set myRange = mySheet.Range("NameList")

On Error Resume Next ' Without this code will fall over if strName is
not in the list
ListIndex = Excel.WorksheetFunction.Match(strName, myRange, EXACT_MATCH)

If Err.Number = 0 Then
myRange.Cells(ListIndex, 2).value = lngAge
ElseIf Left(Err.Description, 23) = BAD_MATCH Then
MsgBox "Name: " & strName & " not found"
Else 'Unexpected Error
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description
End If
On Error GoTo 0

Set mySheet = Nothing
Set myRange = Nothing
End Sub

HTH

"Tod" wrote in message
...
| Okay, I'm going to try something new, so everybody duck!!
|
| I want to have an array that already has one dimension
| filled in, then have the second dimension filled in at
| run time. So if the first dimension is:
|
| Earl
| Larry
| Fred
| Janet
| Carrie
|
| I want to add values based on the name. The values will
| not always come in the same order as the names. Is there
| a way that I can "search" the array for the name and then
| add the value next to the match?
|
| tod
|
|
|