Is there a way to output the value in one column based on two others?

Question

I am attempting to create output for one column in my table by basing it off of the values in two other columns.

I've tried working with the variables and think I have gotten the loop to work. The issues I keep running into are "Mismatch" and "Global"

Private Sub formatcolumnF()

Dim eqa As Range, eqt As Range

Set eqa = ActiveSheet.Range("D2", Range("D2").End(xlDown))
Set eqt = ActiveSheet.Range("C2", Range("C2").End(xlDown))

Dim result As String, Cell As Range

For Each Cell In eqa
     If Cell >= eqt.Value + 0.025 Then
            result = "OVER"
     ElseIf Cell <= eqt.Value - 0.025 Then
            result = "UNDER"
     ElseIf Cell <= eqt.Value + 0.025 Or Cell >= eqt.Value - 0.025 Then
            result = "ON TARGET"
     Else
            result = ""
     End If
Next Cell

Range("F2", Range("F2").End(xlDown)).Value = result

End Sub

I expect the output in column F to be one of the string results. When I run it for a specific row in the table the code works but when I try to run it for the whole column it does not.

`eqt` is a range and therfore one cannot equate a full range to one cell. You will need another loop to loop the second range as well. — Scott Craner, Apr 23 '19 at 20:06
Your understanding of how to reference cells when using a For Each loop is incorrect with respect to your use of eqt. The for each only relates to eqa. You will be better served by reading the two ranges into VBA arrays and then iterating over the arrays from Lbound to Ubound. — freeflow, Apr 23 '19 at 20:07
You don't need VBA for this, Excel functions are sufficient. — z32a7ul, Apr 23 '19 at 20:20
It may be easier to read these ranges into an array, like `arr_a = ActiveSheet.Range("D2", ActiveSheet.Range("D2").End(xlDown)).Value2` and `arr_t` similarly. Then, you can `Dim i As Integer` and do your loop over the values of `arr_a` and `arr_t` with `For i = LBound(arr_a, 1) To UBound(arr_a,1)` and `If arr_a(i, 1) >= arr_t(i, 1) Then` blah blah — jessi, Apr 23 '19 at 20:23

Vityata · Answer 1 · 2019-04-24T14:38:59.037

This one works:

Private Sub FormatcolumnF()

    Dim eqa As Range, eqt As Range

    Set eqa = ActiveSheet.Range("D2", Range("D2").End(xlDown))
    Set eqt = ActiveSheet.Range("C2", Range("C2").End(xlDown))

    Dim result As String, Cell As Range

    For Each Cell In eqa.Cells
        If IsNumeric(Cell) Then
            If Cell.Value >= Application.Sum(eqt.Value) + 0.025 Then
                Cells(Cell.Row, "F") = "OVER"
            ElseIf Cell <= Application.Sum(eqt.Value) - 0.025 Then
                Cells(Cell.Row, "F") = "UNDER"
            ElseIf Cell <= Application.Sum(eqt.Value) + 0.025 _
            Or Cell >= Application.Sum(eqt.Value) - 0.025 Then
                Cells(Cell.Row, "F") = "ON TARGET"
            End If
        End If
    Next Cell

End Sub

The problem in the code was mainly that eqt.Value does not return value, because the eqt range is multiple cells. Thus, Application.Sum(eqt.Value) should be used. Furthermore, I have added an additional check for whether the cell is numeric in the loop here - If IsNumeric(Cell) Then. At the end, with the CElls(Cell.Row, "F") the corresponding cell in row "F" is updated with OVER and UNDER. The code could be improved further, with avoiding ActiveSheet and declaring the sheet, on which is worked. Take a look at the dots, before Range and Cells, they refer to the worksheet in the With.

How to avoid using Select in Excel VBA

Option Explicit

Private Sub FormatcolumnF()

    Dim eqa As Range, eqt As Range
    Dim wks As Worksheet
    Set wks = ThisWorkbook.Worksheets(1) 
    'or even better by name like "Worksheets(Table1)"

    With wks
        Set eqa = .Range("D2", .Range("D2").End(xlDown))
        Set eqt = .Range("C2", .Range("C2").End(xlDown))
    End With

    Dim result As String, cell As Range

    With wks
        For Each cell In eqa.Cells
            If IsNumeric(cell) Then
                If cell.Value >= Application.Sum(eqt.Value) + 0.025 Then
                    .Cells(cell.Row, "F") = "OVER"
                ElseIf cell <= Application.Sum(eqt.Value) - 0.025 Then
                    .Cells(cell.Row, "F") = "UNDER"
                ElseIf cell <= Application.Sum(eqt.Value) + 0.025 _
                    Or cell >= Application.Sum(eqt.Value) - 0.025 Then
                    .Cells(cell.Row, "F")  = "ON TARGET"
                End If
            End If
        Next cell
    End With

End Sub

So the first snippet of code works partially as right now I am getting "Under" for every cell when they are not all under. The second snippet of code gives me a 1004 error. — tony twotime, Apr 24 '19 at 13:31
@tonytwotime - change `ThisWorkbook.Worksheets(1)` to `ActiveSheet` in the second snippet. Where is the error, btw? — Vityata, Apr 24 '19 at 13:41
it is in this line `.Cells(cell.Row, "F").Text = "UNDER"` prior to that it was in the line `.Cells(cell.Row, "F").Text = "ON TARGET"` — tony twotime, Apr 24 '19 at 14:15
@tonytwotime - I removed the `.Text` part. Now it should be working. — Vityata, Apr 24 '19 at 14:39
Even with the changes it is still outputting only "UNDER" in column F — tony twotime, Apr 24 '19 at 15:24
@tonytwotime - the condition is this one - `Application.Sum(eqt.Value) - 0.025`, Are you sure it is not the input? Try "walking" step-by-step with F8 and see the corresponding values. — Vityata, Apr 24 '19 at 17:17

Is there a way to output the value in one column based on two others?

1 Answers1