Copying Addresses to New Sheet

[JADownie]

Hi,

I have the code below that I was trying to use to flag addresses where the state was AK or HI (State is in Col J on sheet Address Details). I am trying to copy all of these values to the sheet AK_HI Records but I keep getting a runtime error.

Any help here would be greatly appreciated.



Sub H_Copy_AK_HI()
Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean

strArray = Array("AK", "HI")

Set wsSource = ActiveSheet

NoRows = wsSource.Range("A100000").End(xlUp).Row
DestNoRows = 1
Set wsDest = ActiveWorkbook.Sheets("AK_HI Records")

For I = 1 To NoRows

Set rngCells = wsSource.Range("I" & J & ":I" & J)
Found = False
For J = 0 To UBound(strArray)
Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
Next J

If Found Then
rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)

DestNoRows = DestNoRows + 1
End If
Next I

Sheets("Address Details").Select




End Sub

 

[Fluff]

What are you trying to do with the line in red?

 

[JADownie]

The red is the line where I am getting the runtime error now

 

[Fluff]

Yes but what is it meant to be doing?

 

[JackDanIce]

What value is J for the line in red? You've declared J at the top as an Integer (should be Long if using Excel 2007 or later), which initially has value 0 so you're effectively setting

Set rngCells = wsSource.Range("I0:I0")

 

[Fluff]

@JackDanIce
I'm thinking that it should really be

Set rngCells = wsSource.Range("J"& I)

As the Op is looking for the state in col J.
Hence trying to seek clarification

 

[JackDanIce]

Hey @Fluff, think you're right, see if that helps OP. I just saw J declared as Integer and first place it was used was in the line in red