load data into array, check condition, and paste back into sheet

Hi all,

I want to check the condition of multiple cells in a range without creating a helper column (for convenience and to learn more about VBA).

In my sample file, column A will stay the same, but values in column B have to be adjusted based on my condition (cell in column B divided by cell in column A should not exceed 0.95). I created two helper columns to illustrate what I want to achieve. Column C uses the cell reference of column B to check for the condition, whereas column D uses the cell value of column B instead. I assume this could be done with code by loading the values of column B into an array, then checking if the condition is met and pasting it back into the range?

What should happen if a cell in col B doesn't meet the criteria? change its value to 'warning'?

Try on a copy of your workbook.
Changes the values in Column B to 95 percent if it is more.

Sub Maybe()
Dim a, lr As Long, i As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    a = Range("A2:B" & lr).Value
        For i = LBound(a) To UBound(a)
            If Not a(i, 1) = 0 Then
                If a(i, 2) / a(i, 1) > 0.95 Then a(i, 2) = a(i, 1) * 0.95
            End If
        Next i
Range("A2").Resize(UBound(a), 2) = a
End Sub

Originally Posted by jolivanes

Try on a copy of your workbook.
Changes the values in Column B to 95 percent if it is more.

This was a very good starting point! Thanks jolivanes!!

Below is what seems to work for me now, even though not sure if it's the most efficient solution.

            Dim rr As Long
            
            For rr = 1 To 3
            
                Region = Choose(rr, "HMT", "CHN", "SEA")
                
                Dim vv As Variant
                Dim ii As Long
                Dim iii As Long
    
                With Range("Metric_" & Region & "_FCST_SLS_UNT")
                    '// Load cell values into array x
                    vv = .Value
                    '// Loop through both dimensions (if more than one) of array x
                    For ii = 1 To UBound(vv, 1)
                        For iii = 1 To UBound(vv, 2)
                            If Range("Attribute_Status").Item(ii, iii).Value = "STD" Then
                                If IsError(Range("Metric_" & Region & "_FCST_SLS_UNT").Item(ii, iii).Value) = True Then
                                    vv(ii, iii) = "0"
                                Else
                                    vv(ii, iii) = "=IFERROR(IF(" & Range("Metric_GCSEA_FCST_SLS_UNT").Item(ii, iii).Value & "/" & ColName(Range("Metric_GCSEA_DTC_OO_UNT")) & Range("Metric_" & Region & "_FCST_SLS_UNT").Item(ii, iii).Row & ">0.95,ROUND(" & ColName(Range("Metric_GCSEA_DTC_OO_UNT")) & Range("Metric_" & Region & "_FCST_SLS_UNT").Item(ii, iii).Row & "*0.95*(" & vv(ii, iii) & "/" & Range("Metric_GCSEA_FCST_SLS_UNT").Item(ii, iii).Value & "),0)," & vv(ii, iii) & "),0)"
                                End If
                            End If
                        Next iii
                    Next ii
                    '// Post updated formulas in array x back to the sheet
                    .Formula = vv
                    '.Value = .Value
                End With
                
            Next rr

Originally Posted by Akuini

What should happen if a cell in col B doesn't meet the criteria? change its value to 'warning'?

Yes, exactly! If the value exceeds 0.95 it should paste the string "Warning!". I simplified the actual output for explanation purposes.