Excel: Can anyone change my code to another that uses Array?


This is my code right now, I set the j and i start from 4, because the list starts from the 4th row. I have a list of people with the number assigned to each of them at column A and B. I want to make a new list of people who has the number greater than 500 at column D and E. Can anyone fix this code to another that uses Arrays to insert the values I want at Column D and E? Sub MyCode () j = 4 For i = 4 To Range("A" & Rows.Count).End(xlUp).Row If Range("B" & i).Value >= 500 Then Range("D" & j).Value = Range("A" & i).Value Range("E" & j).Value = Range("B" & i).Value j = j + 1 End If Next i End Sub This is the code I'm trying to make, but not working.. Sub NewCode () Dim Names(1 to 93) as String Dim Numbers(1 to 93) as Integer Dim i as Integer, j as Integer j = 4 to 96 For i = 4 To 96 Names() = Range("A" & j).Value Amounts() = Range("B" & i). Value If Amounts() >= 500 Then Range("D" & j).Value = Names().Value Range("E" & j).Value = Amounts().Value j = j + 1 End If Next i End Sub Can't assign Array,,, don't know why.. Anyone can help me out? Hope that new list in column D and E does not have blank cells between each values.


This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of April 26, 2015.


For more resources for Microsoft Excel