Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 How do we know which value matches which name? Alan Beban |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Use array to return array of values | Excel Worksheet Functions | |||
Convert values in a variant array to integer values | Excel Programming | |||
Adding an Array | Excel Programming |