Using Arrays With IF-THEN Statements in VBA

[Altonfree]

Hello,

I'm trying to write a macro to copy a cell if the first three integers in the adjacent cell are present in an array. I'm brand new to the concept of arrays, and having a difficult time trying to figure out how to proceed. When I run the code below, I get a Type Mismatch error. I've uploaded an example of the worksheet I'm using.

Sub mcrFinancialCodeTemplates1()

Dim aryAMPTH() As Integer
Dim aryBUs As Integer
Dim i As Integer

aryBUs = "180, 181, 182, 184, 185, 751, 752, 753, 754, 755, 757, 758, 759, 762, 765, 766, 771, 773, 774, 776, 777, 810, 811, 813, 817, 818, 818, 819, 820, 822, 825, 831, 833, 834, 835, 838, 839, 950, 951, 952, 953, 954, 955, 956, 957, 959, 960, 962, 963, 964, 965, 967, 968, 969, 970, 971, 974, 975, 976, 977"
aryAMPTH = Split(aryBUs, ", ")

For i = LBound(aryAMPTH) To UBound(aryAMPTH)

Sheets("Main").Select
Range("A1").Activate
Do While Not IsEmpty(ActiveCell.Value)
If (Left(ActiveCell, 3) = aryAMPTH) Then
ActiveCell.Offset(0, 1).Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -2) = "AMPTH"

Any help would be appreciated!

[AB33]

Please use code tags with your code as per forum's rule.
There are a couple of issue with your code.

Unless you are going to assign a range in to array, the way you have done is wrong. All values with the array must be put in parenthesis.

I have done few of them for you, but you need to do the rest. I have also corrected some of the errors, but you will encounter further errors.

Please Login or Register  to view this content.

[Altonfree]

Thank you. I added the quotation marks, and am still getting the type mismatch error.

Please Login or Register  to view this content.

[AB33]

Change this line

Dim aryBUs As Integer

In to

Please Login or Register  to view this content.

[Norie]

You don't need "" around every item, keep it as a string like it was in your first post.

This will compile, but it won't work - think it might send you into an infinite loop.

The reason it doesn't work is that you never advance the active cell.

Please Login or Register  to view this content.

What is it you actually want the code to do?

This code will move the values from column B to Sheet2 when the corresponding value in A matches one of the values in the array.

Please Login or Register  to view this content.