Value equal to ANY value in an array

[swimd01]

I am working with the following variables in a script I am writting:

X: a single string variable
A(1 - 18): an array that contains 18 strings

I have a loop that cycles through a column of numbers (lets say column "B"), and sets X equal to B1, then B2, the B3, etc...

I would like to add an If/then statement into into the loop that will do the following:

If: X.value <> any value in the array A(1 - 18)
Then: delete the row
Else: do nothing, and move on

Is this possible? If so, can anyone help with some code? Thanks

 

[Jonmo1]

Try

If IsError(Application.Match(X.Value, A, 0)) Then X.EntireRow.Delete

 

[mrmmickle1]

Try using Filter. It will only work with one dimensional arrays from what I understand:

[COLOR=#0000ff]Sub [/COLOR]Test()

[COLOR=#0000ff]Dim[/COLOR] FindStr  [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] MyArr  [COLOR=#0000ff]  As Variant[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] x        [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]ArrChk   [COLOR=#0000ff]As Variant[/COLOR]

FindStr = "Not in Array"
MyArr = Array("Matt", "John", "Gerry", "Wesley", "Chris", "Mary", "Ann", "Irene")

ArrFilter = Filter(MyArr, FindStr)

[COLOR=#0000ff]For[/COLOR] x = 1 [COLOR=#0000ff]To[/COLOR] 10
  [COLOR=#0000ff]  If UBound[/COLOR](ArrLp) < 0 [COLOR=#0000ff]Then[/COLOR][COLOR=#008000] 'If Less than 0 the value is not found in the array[/COLOR]
        Cells(x, 2).EntireRow.Delete
  [COLOR=#0000ff]  End If[/COLOR]
[COLOR=#0000ff]Next[/COLOR] x
    
[COLOR=#0000ff]End Sub[/COLOR]

 

[jasonb75]

Looping is a longwinded way to do it if you have a lot of rows.

A method I suggested a while back seems to work quite well, but I can't find the original thread, this is just some quick code to show the theory.

Filter to show the records you don't want to delete, hide visible rows and clear the filter to invert the selection, then delete the rows that are still visible.

Jonmo, can you correct / tweak as needed please? I'm going to be AFK for most of the day.

With ActiveSheet.Range("$B$2:$B$500")
    .AutoFilter Field:=1, Criteria1:=Array("your array"), Operator:=xlFilterValues
    .SpecialCells(xlVisible).EntireRow.Hidden = True
    .ShowAllData
    .SpecialCells(xlVisible).EntireRow.Delete
    .EntireRow.Hidden = False
End With

 

[swimd01]

OK, so I am using Jonmo1's code, If IsError(Application.Match(X.Value, A, 0)) Then X.EntireRow.Delete

This code works great for what I am doing until I encounter the situation where one of the values in the A(1 - 18) array is not present in the data that I am searching.

i.e. if A(5) = 115, and 115 does not exist anywhere in column "B", so application.match returns an "N/A" error.

I would like the cose to just skip this value, and move on to A(6). How can this be done? I'm not sure how to "nest" functions in vba, but I imagine that an "If(iserror(" check, where that case is set to zero, would take care of this problem.

Anybody have any ideas? Thanks

 

[Jonmo1]

I don't understand the problem..
That seems the opposite of what you originally wanted.

You wanted to Search A for X
But now you're saying if A(6) doesn't exist in X..

Can you post the actual code you're using, and describe what it's supposed to do in detail?

 

[swimd01]

OK,

Here is my code:

Sub AcntNum()

Dim R As Integer 'original # of rows
Dim R1 As Integer 'new # of rows after sort
Dim i As Integer 'counter
Dim i2 As Integer 'counter 2
Dim S1 As Worksheet 'manipulation sheet definition
Set S1 = ThisWorkbook.Worksheets("1")
Dim Aws As Worksheet 'account worksheets
Dim RowB As Integer 'account start on sheet 1
Dim RowE As Integer 'account end on sheet 1
Dim RowL1 As Long 'last row on account worksheet before paste
Dim RowL2 As Long 'last row on account worksheet after paste

Dim Arr(1 To 18) As Variant 'account # array
Arr(1) = 7710541
Arr(2) = 7709192
Arr(3) = 7709207
Arr(4) = 7709223
Arr(5) = 7709231
Arr(6) = 7709249
Arr(7) = 7709265
Arr(8) = 7709273
Arr(9) = 7709299
Arr(10) = 7709312
Arr(11) = 7709338
Arr(12) = 7709354
Arr(13) = 7709370
Arr(14) = 7709388
Arr(15) = 7709396
Arr(16) = 7709401
Arr(17) = 7709435
Arr(18) = 7709540


S1.Cells.Delete
ThisWorkbook.Sheets("D").UsedRange.Copy
S1.Select
S1.Range("A1").Select
S1.Paste
Range("A1").Select
Selection.End(xlDown).Select
R = ActiveCell.Row

'move columns on sheet 1
Range("A:A,C:C,G:G").Delete
Range("C:C").Cut
Range("B:B").Insert

Range("G:G").Cut
Range("D:D").Insert

Range("C:C").Cut
Range("I:I").Insert

'eliminate non-tracked accounts
For i = 2 To R
If IsError(Application.Match(S1.Range("A" & i).Value, Arr, 0)) Then
    S1.Range("A" & i).EntireRow.ClearContents
End If
Next i
'sort remaining values
S1.Range("A2:A" & R).EntireRow.Select
S1.Sort.SortFields.Clear
S1.Range("A2:A" & R).EntireRow.Sort key1:=S1.Range("B2"), Order1:=xlAscending
S1.Range("A1").Select
Selection.End(xlDown).Select
R1 = ActiveCell.Row
S1.Range("A1").Select
Application.CutCopyMode = False

'Type definition
For i = 2 To R1
If S1.Cells(i, 3) = "" Then
S1.Cells(i, 3).Value = Application.Proper(S1.Cells(i, 6).Value)
End If
Next i

S1.Range("C:C").Select
Selection.Replace what:=" Debit", replacement:=""
Selection.Replace what:=" Credit", replacement:=""
S1.Range("D:D").Select
Selection.Replace what:=" ", replacement:=""

'Transaction ID
S1.Range("F:F").Delete
S1.Range("G:G").Insert
S1.Range("G1").Value = "Transaction ID"
S1.Range("G2").Formula = "=A2&B2&D2&E2&C2"
S1.Range("G2:G" & R1).FillDown

'format Sheet 1
Cells.Select
With Selection
    .ColumnWidth = 10
    .RowHeight = 15
    .VerticalAlignment = xlCenter
    .HorizontalAlignment = xlLeft
    .Font.Name = "Arial Narrow"
    .Font.Size = 8
End With

Range("G:G, H:H, I:I").ColumnWidth = 30
Range("B:B").NumberFormat = "m/d/yyyy"
Range("A:A, D:D").NumberFormat = "0"
Range("A:E").HorizontalAlignment = xlRight
Range("F:F").NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("A1:J1").Font.Bold = True
Range("A1:H1").Borders(xlEdgeBottom).Weight = 3
Application.CutCopyMode = False

Stop
'paste sheet values
For i = 1 To 18
    Set Aws = ThisWorkbook.Worksheets(CStr(Arr(i)))
    Stop
    RowL1 = Aws.Range("A65536").End(xlUp).Row
    Stop
    RowB = Application.Match(Arr(i), S1.Range("A:A"), 0)
    Stop
    RowE = RowB - 1 + Application.CountIf(S1.Range("A:A"), Arr(i))
    Stop
    RowL2 = RowL1 + RowE - RowB + 1

    MsgBox "RowL1: " & RowL1
    MsgBox "RowL2: " & RowL2
    MsgBox "RowB: " & RowB
    MsgBox "RowE: " & RowE
Stop
    S1.Range("A" & RowB & ":A" & RowE).EntireRow.Copy
    Aws.Select
    Aws.Cells(RowL1 + 1, 1).Select
    Aws.Paste
    ' remove duplicates
    Aws.Range("I1").Formula = "=max(I2:I10000)"
    Aws.Range("I2").Formula = "=IF(ISERROR(MATCH(G2,$G:$G,0)),0,MATCH(G2,$G:$G,0))"
    Aws.Range("I2" & ":I" & RowL2).Select
    Selection.FillDown

        For i2 = 2 To RowL2
        If Aws.Range("I" & i2).Value <> i2 Then
        Aws.Range("I" & i2).EntireRow.ClearContents
        End If
        Next i2
    Aws.Sort.SortFields.Clear
    Aws.Range("A2:A" & RowL2).EntireRow.Sort key1:=Aws.Range("B2"), Order1:=xlAscending
Next i


End Sub

Sorry I wasn't wore clear...

There are 18 account #s A(1 to 18). You can see the account #s in the code.

There is a daily dump of data which includes transaction data from about 60 accounts. Each line (row) is a single transaction, relating to a single account, but on a given day, there may be multiple transactions relating to a signle account, or no transactions.

This code is meant to look for all of the individual transactions for a given account, say account # A(1). Lets say for instance that there are 10 transactions (on 10 lines) for account A(1). The code copies those 10 lines to a worksheet with the name A(1). Then, does the same thing for Accounts A(2), A(3)... A(18)

Where I am running into a problem, is when there is no data in the daily dump for a given account. For example, if today account A(5) had no transactions, the code results in a error. In this case, I want to just go on to account A(6).

I apologize if this is isn't clear. Please let me know if I can clarify anything else for you. I really appreciate all the help.

 

[Jonmo1]

You could reverse the logic

Instead of
If IsError(Application.Match(X.Value, A, 0)) Then X.EntireRow.Delete

Try
If Not IsError(Application.Match(X.Value, A, 0)) Then X.EntireRow.Delete

Now when the match is #N/A, the delete row command won't happen, and it will not error.

 

[swimd01]

The piece of the code that errors out is:

RowB = Application.Match(Arr(i), S1.Range("A:A"), 0)

I have tried to modify this to:

If Not IsError(RowB = Application.Match(Arr(i), S1.Range("A:A"), 0)) Then
        RowB = Application.Match(Arr(i), S1.Range("A:A"), 0)
    End If

Neither works. I get the error "Runtime error '13': Type mismatch" when one of the account numbers does not have data on that day.

Is there a way to fix this?

 

[Jonmo1]

Are you sure it's not the next line that errors?
This won't error
RowB = Application.Match(Arr(i), S1.Range("A:A"), 0)
if match is #N/A, it assigns the #N/A error to the variable Rowb
But in that case, your next line will error because RowB contains an error value.
RowE = RowB - 1 + Application.CountIf(S1.Range("A:A"), Arr(i))

try

RowB = Application.Match(Arr(i), S1.Range("A:A"), 0)
If IsError(RowB) Then
    'Do Nothing
Else
    'Put the rest of your code that was inside the loop here
End If