Looping through an array in Excel

Question

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

So I altered it to look for entries not equal to "". This code is now not skipping the blank entries and still won't go to the next column "AN" to pull more values. 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 <> "" Then S3.Cells(j, 2).Value = S2.Cells(i, 1).Value j = j + 1 End If Next i — lilsilenttigr1
– lilsilenttigr1, Commented Jul 10, 2019 at 14:42

Dominique · Accepted Answer · 2019-07-10 13:45:54Z

1

Replace:

<> "#N/A"

By:

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

answered Jul 10, 2019 at 13:45

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lilsilenttigr1 Over a year ago

It still is not looping through the other columns though. How can that be fixed?

LafaMan · Accepted Answer · 2019-07-11 13:11:54Z

1

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

edited Jul 11, 2019 at 13:11

answered Jul 10, 2019 at 15:45

LafaMan

1801 silver badge12 bronze badges

4 Comments

LafaMan Over a year ago

You would need to set the for loop of j to the number of columns you want to cycle through and the for loop of i for the number of rows

lilsilenttigr1 Over a year ago

So this does work for me in getting all the data over, but now it doesn't condense them. it essentially just looks like a copy/paste values but instead of the errors in the cells, they are just blank.

lilsilenttigr1 Over a year ago

Thank you! It works great! I put multiple "Call"s to take care of multiple blank cells, but it is fantastic!

LafaMan Over a year ago

Mark it as correct if it worked for you. It might help someone else

Hichem CHTARA · Accepted Answer · 2019-07-10 13:49:44Z

0

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

answered Jul 10, 2019 at 13:49

Hichem CHTARA

251 silver badge7 bronze badges

Collectives™ on Stack Overflow

Looping through an array in Excel

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related