VBA Array not working - cannot write to array and read is too slow

[ouvay]

Hello

I am writing a code to write this code to populate an array based on an If statement and then unload that array into a range.

Here is a sample of my code

Sub UpdateFromPCT()

Dim wkb As Workbook
Dim ws As Worksheet
Dim pct As Workbook
Dim pctST As Worksheet
Dim i As Long
Dim holdAry() As Variant
Dim Count As Integer

Count = 0

Set wkb = ThisWorkbook
Set ws = wkb.Worksheets(1)
Set pct = Workbooks.Open("C:\Users\user\OneDrive - Enagic USA Inc\General\Payment Check Tool.xlsm")
Set pctST = pct.Worksheets("Batch Processing & Hold List")


With pctST

lastRow = .Range("C6").End(xlDown).Row
For i = 6 To lastRow
ReDim holdAry(1 To lastRow + 5) As Variant
If .Range("K" & i).Value = "Tokurei" Or .Range("K" & i).Value = "OK" Then
Else
Count = Count + 1
holdAry(Count) = .Range("C" & i).Value
End If
Next i
End With


With ws.Range("N2:N700")
For i = LBound(holdAry) To UBound(holdAry)
.Range("N" & i).Value = holdAry(i)
Next i
End With

End Sub

For some reason this code returns blanks... it appears to be blank when I debug.print to immediate window and I'm not sure what I'm doing wrong.

I did however get it to work once by fluke, but the code took forever to work.. if you could help factor in a faster version of this for me to work off of, I would appreciate it!!

 

[mohadin]

Hi
Try

'Option Explicit

Sub UpdateFromPCT()

    Dim wkb As Workbook
    Dim ws As Worksheet
    Dim pct As Workbook
    Dim pctST As Worksheet
    Dim i As Long
    Dim holdAry() As Variant
    Dim Count As Integer
    Count = 0

    Set wkb = ThisWorkbook
    Set ws = wkb.Worksheets(1)
    Set pct = Workbooks.Open("C:\Users\user\OneDrive - Enagic USA Inc\General\Payment Check Tool.xlsm")
    Set pctST = pct.Worksheets("Batch Processing & Hold List")

    With pctST
        lastrow = Cells(Rows.Count, 3).End(xlUp).Row
        ReDim holdAry(1 To lastrow + 5)

        For i = 6 To lastrow
            If .Range("K" & i).Value = "Tokurei" Or .Range("K" & i).Value = "OK" Then
            Else
                Count = Count + 1
                holdAry(Count) = .Range("C" & i).Value
            End If
        Next i
    End With


    With ws.Range("N2:N700")
        For i = LBound(holdAry) To UBound(holdAry)
            .Range("N" & i).Value = holdAry(i)
        Next i
    End With

End Sub

 

[ouvay]

Hi

Hi
Try

'Option Explicit

Sub UpdateFromPCT()

    Dim wkb As Workbook
    Dim ws As Worksheet
    Dim pct As Workbook
    Dim pctST As Worksheet
    Dim i As Long
    Dim holdAry() As Variant
    Dim Count As Integer
    Count = 0

    Set wkb = ThisWorkbook
    Set ws = wkb.Worksheets(1)
    Set pct = Workbooks.Open("C:\Users\user\OneDrive - Enagic USA Inc\General\Payment Check Tool.xlsm")
    Set pctST = pct.Worksheets("Batch Processing & Hold List")

    With pctST
        lastrow = Cells(Rows.Count, 3).End(xlUp).Row
        ReDim holdAry(1 To lastrow + 5)

        For i = 6 To lastrow
            If .Range("K" & i).Value = "Tokurei" Or .Range("K" & i).Value = "OK" Then
            Else
                Count = Count + 1
                holdAry(Count) = .Range("C" & i).Value
            End If
        Next i
    End With


    With ws.Range("N2:N700")
        For i = LBound(holdAry) To UBound(holdAry)
            .Range("N" & i).Value = holdAry(i)
        Next i
    End With

End Sub

Hi Mohadin,

I your code and it worked perfectly!

I'm not sure entirely sure what I was doing differently, so if you could explain, I'd be grateful, but I'm happy it works none the less.

Thanks for your help!

 

[mohadin]

Well,
lastrow should be like

 lastrow = Cells(Rows.Count, 3).End(xlUp).Row

And

 ReDim holdAry(1 To lastrow + 5)

Should be out the for loop, you do Redim only one time right?

 

[ouvay]

Well,
lastrow should be like

 lastrow = Cells(Rows.Count, 3).End(xlUp).Row

And

 ReDim holdAry(1 To lastrow + 5)

Should be out the for loop, you do Redim only one time right?

ah! that makes sense!!! thank you so much for explaining where I went wrong!

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy and safe

 

[bebo021999]

In both codes in #1 and #2:

For i = LBound(holdAry) To UBound(holdAry)
            .Range("N" & i).Value = holdAry(i)
        Next i

it loop through each row in sheet then unload array then paste

Why not, in bulk:

.Range("N1").Resize(Ubound(holdAry),1).value = holdAry

???

 

[ouvay]

In both codes in #1 and #2:

For i = LBound(holdAry) To UBound(holdAry)
            .Range("N" & i).Value = holdAry(i)
        Next i

it loop through each row in sheet then unload array then paste

Why not, in bulk:

.Range("N1").Resize(Ubound(holdAry),1).value = holdAry

???

I tried this! seems like it should work, but the output is just the value holdAry(1)

this is how I tried it

...
With ws
.Range("N2").Resize(UBound(holdAry), 1).Value = holdAry
.Range("N1").Value = Format(Date, "yyyy/mm/dd")
...