Lets say I have a workbook with ID numbers in it. I have a macro
doing a lot of work on this workbook and one of the things I want it
to do is to convert the ID numbers into names. I haven't done this
with VB before and was wondering what the best/easiest way to do this
would be? If I wasn't doing it with a macro, I would just have a
vlookup, but this isn't an option for my case. I have looked into
arrays, but this seems like it would be hard to populate and search
thru. My idea was to create two arrays:

IDs = Array (1111,2222,3333,4444)
Names = Array("Bob","John","Henry","Sue")

The real data would contain about 20 elements. I would rarely if ever
need to add or change IDs/Names. Then as I find the ID value in the
cell, it would look thru the ID array, find the index of the value,
the pull up the corresponding index in the names array. This seems
cumbersome and I am sure there has to be a better way to do this.

Thanks,
Andrew V. Romero

On Apr 7, 11:16*am, "
wrote:
Lets say I have a workbook with ID numbers in it. *I have a macro
doing a lot of work on this workbook and one of the things I want it
to do is to convert the ID numbers into names. *I haven't done this
with VB before and was wondering what the best/easiest way to do this
would be? *If I wasn't doing it with a macro, I would just have a
vlookup, but this isn't an option for my case. *I have looked into
arrays, but this seems like it would be hard to populate and search
thru. *My idea was to create two arrays:

IDs = Array (1111,2222,3333,4444)
Names = Array("Bob","John","Henry","Sue")

The real data would contain about 20 elements. *I would rarely if ever
need to add or change IDs/Names. *Then as I find the ID value in the
cell, it would look thru the ID array, find the index of the value,
the pull up the corresponding index in the names array. *This seems
cumbersome and I am sure there has to be a better way to do this.

Thanks,
* Andrew V. Romero

With only twenty items, that wouldn't be cumbersome at all, and will
work OK.

outvalue = "" 'clear previous value
For iCounter = 1 to 20
if Invalue = ID(iCounter) then
OutValue = Names(icounter)
End if
next icounter

if outvalue = "" then
call msgbox( .... ) 'no result
else
'whatever you need to do
end if



HTH

Chris

While it would work, I am hoping there is a more streamlined approach
because I am going to have to have scan about 40,000 ID records. It
seems like looping thru an array 40,000 times isn't the best way to do
this.

On Apr 7, 8:36*am, cht13er wrote:
On Apr 7, 11:16*am, "
wrote:





Lets say I have a workbook with ID numbers in it. *I have a macro
doing a lot of work on this workbook and one of the things I want it
to do is to convert the ID numbers into names. *I haven't done this
with VB before and was wondering what the best/easiest way to do this
would be? *If I wasn't doing it with a macro, I would just have a
vlookup, but this isn't an option for my case. *I have looked into
arrays, but this seems like it would be hard to populate and search
thru. *My idea was to create two arrays:

IDs = Array (1111,2222,3333,4444)
Names = Array("Bob","John","Henry","Sue")

The real data would contain about 20 elements. *I would rarely if ever
need to add or change IDs/Names. *Then as I find the ID value in the
cell, it would look thru the ID array, find the index of the value,
the pull up the corresponding index in the names array. *This seems
cumbersome and I am sure there has to be a better way to do this.

Thanks,
* Andrew V. Romero

With only twenty items, that wouldn't be cumbersome at all, and will
work OK.

outvalue = "" * * * * *'clear previous value
For iCounter = 1 to 20
* *if Invalue = ID(iCounter) then
* * * * OutValue = Names(icounter)
* End if
next icounter

* if outvalue = "" then
* * * call msgbox( .... ) 'no result
*else
* * * *'whatever you need to do
* end if

HTH

Chris- Hide quoted text -

- Show quoted text -

On Apr 7, 4:20*pm, "
wrote:
While it would work, I am hoping there is a more streamlined approach
because I am going to have to have scan about 40,000 ID records. *It
seems like looping thru an array 40,000 times isn't the best way to do
this.

On Apr 7, 8:36*am, cht13er wrote:



On Apr 7, 11:16*am, "
wrote:

Lets say I have a workbook with ID numbers in it. *I have a macro
doing a lot of work on this workbook and one of the things I want it
to do is to convert the ID numbers into names. *I haven't done this
with VB before and was wondering what the best/easiest way to do this
would be? *If I wasn't doing it with a macro, I would just have a
vlookup, but this isn't an option for my case. *I have looked into
arrays, but this seems like it would be hard to populate and search
thru. *My idea was to create two arrays:

IDs = Array (1111,2222,3333,4444)
Names = Array("Bob","John","Henry","Sue")

The real data would contain about 20 elements. *I would rarely if ever
need to add or change IDs/Names. *Then as I find the ID value in the
cell, it would look thru the ID array, find the index of the value,
the pull up the corresponding index in the names array. *This seems
cumbersome and I am sure there has to be a better way to do this.

Thanks,
* Andrew V. Romero

With only twenty items, that wouldn't be cumbersome at all, and will
work OK.

outvalue = "" * * * * *'clear previous value
For iCounter = 1 to 20
* *if Invalue = ID(iCounter) then
* * * * OutValue = Names(icounter)
* End if
next icounter

* if outvalue = "" then
* * * call msgbox( .... ) 'no result
*else
* * * *'whatever you need to do
* end if

HTH

Chris- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

See how long it takes with Application.ScreenUpdating = False and
Application.Calculations = xlManual or whatever ....

And with this:

outvalue = "" 'clear previous value
For iCounter = 1 to 20
if Invalue = ID(iCounter) then
OutValue = Names(icounter)
iCounter = 20
End if
next icounter


if outvalue = "" then
call msgbox( .... ) 'no result
else
'whatever you need to do
end if



I bet that even with 40k records it'll run in less than half a
minute ..

HTH

Chris

I agree with cht13er in his comment below about how fast the array populate
should run. I would recommend the following

1. Consider using a multiple dimensional array instead of two separate
arrays; aLookup(1, <totalCount) and use Redim if the data count is not
static.
aLookup(0, #) - Id
aLookup(1, #) - Name

2. Use indirect cell referencing to avoid having to activate worksheets as
you read them.

3. Although you can probably use Integer variable type for your counter, I
use Double instead of Integer when working with numbers over 32K.

I got curious about this so I wrote a test subroutine to populate an array
from a worksheet containing 40k rows with 2 columns of data. I actually
create a 3rd string combining column 1 and column 2 to make it a bit more
complex:

CodeBlock:

Sub testarray()

Dim aTempArr()
Dim iArrCtr, iRowCtr As Double
iArrCtr = -1
iRowCtr = 1

Do While ActiveSheet.Cells(iRowCtr, 1) < ""
iArrCtr = iArrCtr + 1
ReDim Preserve aTempArr(2, iArrCtr)
aTempArr(0, iArrCtr) = ActiveSheet.Cells(iRowCtr, 1)
aTempArr(1, iArrCtr) = ActiveSheet.Cells(iRowCtr, 2)
aTempArr(2, iArrCtr) = Trim(Str(ActiveSheet.Cells(iRowCtr, 1))) + "
" + ActiveSheet.Cells(iRowCtr, 2)
iRowCtr = iRowCtr + 1
Loop

MsgBox "done"
End Sub

This took 3-4 seconds to run on my laptop (1.6Ghz processor with 1Gb RAM
running Excel 2002).

I just completed a project where as part of a form activate event I build
several multi-dimension arrays, populate 5 comboboxes in an admin/report
setup form, after reading a workbook containing 25 worksheets of payroll
data. By using indirect cell referencing and multi-dimension arrays, the
entire form activate event takes 1-2 seconds to complete.

Please feel free to contact me directly.

Ken


" wrote:

Lets say I have a workbook with ID numbers in it. I have a macro
doing a lot of work on this workbook and one of the things I want it
to do is to convert the ID numbers into names. I haven't done this
with VB before and was wondering what the best/easiest way to do this
would be? If I wasn't doing it with a macro, I would just have a
vlookup, but this isn't an option for my case. I have looked into
arrays, but this seems like it would be hard to populate and search
thru. My idea was to create two arrays:

IDs = Array (1111,2222,3333,4444)
Names = Array("Bob","John","Henry","Sue")

The real data would contain about 20 elements. I would rarely if ever
need to add or change IDs/Names. Then as I find the ID value in the
cell, it would look thru the ID array, find the index of the value,
the pull up the corresponding index in the names array. This seems
cumbersome and I am sure there has to be a better way to do this.

Thanks,
Andrew V. Romero