CODEWITHSUNDEEP
excelvbaloops
lilsilenttigr1
lilsilenttigr1

Reputation: 11

Looping through an array in Excel

Trying to loop through a sheets"data".Range"AM1:AS12" and copy the data to range beginning at BD1 as long as the data doesn't equal "#N/A"

My code works with copying the first column, but doesn't do anything with the data after that. Where am I going wrong?

Set S2 = Sheets("data").Range("AM:AM")
Set S3 = Sheets("data").Range("BD:BD")

Dim i As Integer, j As Integer 

j = 1
For i = 1 To 12 

   If S2.Cells(i, 1).Value <> "#N/A" Then 
      S3.Cells(j, 2).Value = S2.Cells(i, 1).Value 
      j = j + 1 
   End If

Next i

Upvotes: 0

Views: 76

Answers (3)

LafaMan
LafaMan

Reputation: 180

This works when i tested it. 

    Sub CopyCell()

    Set S2 = Sheets("data").Range("A:A")
    Set S3 = Sheets("data").Range("M:M")

    Dim i As Integer, j As Integer

    For j = 1 To 2
    For i = 1 To 12

       If S2.Cells(i, j).Value <> "#N/A" Then
          S3.Cells(i, j).Value = S2.Cells(i, j).Value

       End If

    Next i
    Next j

    Call DeleteBlank

    End Sub



Sub DeleteBlank()

Dim x As Integer
Dim y As Integer

For y = 13 To 16 'Range numbers for the columns the data is copied to
For x = 1 To 10  ' Number of cells of data you want to loop through

If Cells(x, y).Value = "" Then
Cells(x, y).Delete Shift:=xlUp

End If

Next x
Next y

End Sub

Upvotes: 1

Hichem CHTARA
Hichem CHTARA

Reputation: 25

the best thing to is not to check if it is equal to "#N/A" The best is to check if it is an error : If Not (IsError(S2.Cells(i, 1).Value)) Then

Upvotes: 0

Dominique
Dominique

Reputation: 17565

Replace:

<> "#N/A"

By:

Not(Application.WorksheetFunction.IfNa(...))

Upvotes: 1

Related Questions