ADDING 2ND COLUMN TO ARRAY, THEN ONLY OUTPUTTING THE SECOND COLUMN

[UniqueUsername]

I have a table of data in columns A:V. In B, there is a date that I need to check if this date is more recent than the Sunday of the start of the week, 7 weeks ago. Columns C:U contain data or formulas that need to stay as they are, so I'm not able to run the check until column V.

sub updater2000()
Dim sun7Wago As Date
Dim VALS As Variant
Dim I As Long

sun7Wago = Date - 56 'finds the date from 7 weeks ago
sun7Wago = DateAdd("D", -Weekday(sun7Wago) + 1, sun7Wago) 'finds the beginning of the week


VALS = Range("C2:V" & LROW).Value2 'declare my array
For I = 1 To UBound(VALS, 1) 'fill my array
    If VALS(I, 1) > sun7Wago Then
    VALS(I, 20) = "Y"
    Else
    VALS(I, 20) = "N"
    End If
Next I

Range("C2:V" & LROW).Value2 = VALS 'plunk my array back into a table

end sub

This returns the values I need into column V.

I want to be able to only copy column C into the array, create a new column in the array for the Y/N check, then return the 2nd array column into V. Doing this current method removes the formulas I'm using to create some values. I'm also assuming this is poor practice because throwing a lot of unneeded data into the array will slow it down when I scale the project upwards.

Is there an easy way to pull work with the data in the array and return the results to a column outside of the scope of the original array?

Thanks in advance!

-Chris

 

[StephenCrump]

Perhaps something like this? (Assuming you've set a value for LROW outside the Sub?)

Sub updater2000()
    
    Dim sun7Wago As Date
    Dim VALS As Variant
    Dim Output() As String
    Dim I As Long
    
    sun7Wago = Date - 56 'finds the date from 8 weeks ago
    sun7Wago = DateAdd("D", -Weekday(sun7Wago) + 1, sun7Wago) 'finds the beginning of the week
    ReDim Output(1 To LROW - 1, 1 To 1)
        
    VALS = Range("C2:C" & LROW).Value2 'declare my array
    For I = 1 To UBound(VALS) 'fill my array
        If VALS(I, 1) > sun7Wago Then
            Output(I, 1) = "Y"
        Else
            Output(I, 1) = "N"
        End If
    Next I
    
    Range("V2:V" & LROW).Value2 = Output 'plunk my array back into a table

End Sub

Or more succinctly:

Sub updater2000()

    Dim sun7Wago As Long
    
    sun7Wago = Date - 56
    sun7Wago = DateAdd("D", -Weekday(sun7Wago) + 1, sun7Wago)

    'Option 1
    Range("V2:V" & LROW).Value = Evaluate("IF(C2:C" & LROW & ">" & sun7Wago & ",""Y"",""N"")")

    'Option 2
    With Range("V2:V" & LROW)
        .Formula = "=IF(C2>" & sun7Wago & ",""Y"",""N"")"
        .Value = .Value
    End With

End Sub

 

[UniqueUsername]

I tried all 3 options presented above, and ended up using the code given in the first example, where you declare Output() as a String.

Is that effectively creating a 2nd ARRAY? Sorry if that's a basic question, this is my first foray into array

Thank you regardless, kind stranger!

 

[StephenCrump]

Is that effectively creating a 2nd ARRAY?

Yes, that's right. This code line declares a string array. The () indicating an array, with size still to be specified.

Dim Output() As String

This code line sets the correct size. (You can only use a Dim statement to set an array's dimensions if those dimensions are constants, as opposed to variables).

ReDim Output(1 To LROW - 1, 1 To 1)

Incidentally, if you have a variable of type Variant, and set it equal to an Excel range, it automatically sizes appropriately as a 2-D array (with LBound's equal to 1)

Dim VALS As Variant
'...
VALS = Range("C2:V" & LROW).Value2