Help with simple program

Hi,

I’m new at this whole thing. I’m trying to help a friend with a macro (VBA for Excel). Its very simple, he has all these country codes on a spreadsheet. We want the program to read the code and the write the country code next to it. We’ve created a two dimensional array with all the codes and country names.

Here’s the code:

Dim selNum As String

Dim trim As String

Dim Arr(70, 1) As String

Arr(0, 0) = 809280

Arr(0, 1) = "Argentina"

.

.

.

Arr(70, 0) = 809641

Arr(70, 1) = "Zambia"

For i = 2 To Selection.Rows.Count

selNum = Cells(i, 1)

trim = Mid(selNum, 1, 6) ‘trim it from 8 to 6 nums

selNum = Val(trim)

For j = 0 To 2

If selNum = Arr(j, 0) Then

Cells(i, 2) = Arr(j, 1)

End If

Next j

Next I

It’s not working correctly. It only writes 2 countries and the rest is left blank.

Thank you in advance.

Johnny

Answer this question

Help with simple program

Daniel Bogdan
Very stupid mistake… Changed the For j = 0 To 2 to For j = 0 To 70 an now it works perfectly…

I've put a 2 there when I fist wrote the macro to test it an then forgot to update it.

Thanks a lot guys!
Johnny
JimR1
One thing I've noticed: you're only searching through 3 entries in Arr.. the following change is needed:

For j = 0 to 70 ' 70 elements in the Arr array

Also, if it finds it, you'd want to instantly break out of the for loop, so add

Cells(i,2) = Arr(j,1) ' We found the entry, so provide the country code

j = 70 ' set for counter to maximum value so we can exit loop

Hope this helps.
skylar119506

Johnny

Redefine your "For i = 2"

in "For i = 1 to 70"

and it will perform correctly

Regards,Dirk
John G. Yu
Wouldn't your "Selection.Rows.Count" change each loop because you may populate a cell each time through the loop. This would change the 'selection' that the user intially made, wouldn't it

Maybe poutting that value in a separate variable first, beforte the loops, would help.
Help with simple program

Answer this question