Can anyone change my code to another that uses Array?

[kbj0109]

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.

 

[hiker95]

kbj0109,

Here is another macro for you to consider, just in case column B does not contain any numbers greater than 500.

You can change the sheet name in the macro.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub CopyGreaterThan500_V2()
' hiker95, 04/26/2015, ME851317
Dim a As Variant, o As Variant
Dim i As Long, j As Long, n As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  a = .Range("A4:B" & .Range("A" & Rows.Count).End(xlUp).Row)
  .Range("D4:E" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
  n = Application.CountIf(.Range("B4:B" & .Range("A" & Rows.Count).End(xlUp).Row), ">500")
  If n = 0 Then
    MsgBox ("There are no entries in column B greater than 500 - macro terminated!")
    Exit Sub
  End If
  Application.ScreenUpdating = False
  ReDim o(1 To n, 1 To 2)
  For i = 1 To UBound(a, 1)
    If a(i, 2) > 500 Then
      j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 2)
    End If
  Next i
  .Range("D4").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("D4:E" & .Range("A" & Rows.Count).End(xlUp).Row).Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CopyGreaterThan500_V2 macro.

 

[My Aswer Is This]

I also asked him earlier to move his questions here and not to private messages

And me also

 

[hiker95]

kbj0109,

Is your request some type of homework assignment, because, it is almost identical to the following link?

http://www.mrexcel.com/forum/excel-questions/851335-dynamic-array-if-statement.html