Ubound and Lbound

Hi guys,

I was trying to do write a code that reads the 10th column of the row of the activecell and return the country i set in my array "arrcountry". It returns the first value and would not loop through the remaining. Am i doing something wrong here? Thanks.

Dim i As Integer

arrCountry(0, 0) = "USD"
arrCountry(0, 1) = "United State"

arrCountry(1, 0) = "SEK"
arrCountry(1, 1) = "Sweden"

For i = LBound(arrCountry, 1) To UBound(arrCountry, 1)
Set Country = Cells(ActiveCell.row, 10)
If Country = arrCountry(i, 0) Then
ActiveCell = arrCountry(i, 1)
ActiveCell.Offset(1, 0).Select
End If

Next

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Hi,

OK, Understood. But don't forget that you could still use this same technique but control it with a macro. i.e. have the macro temporarily add the VLOOKUP() formula and then copy and paste special values. This would avoid a VBA loop which generally slows things down and be much quicker since it uses a very efficient Excel function rather than slower VBA code.

Regards

Thanks Richard, will try to write it in VBA loop first and try your method next time. thanks.

Hi hommer,
Please find the attached file. Hope this will help you to solve your problem.
Please find the below snapshot of the code I have used in this attached file.

Please Login or Register  to view this content.

Regards,
Daksh