Returning cell value if greater than 0

Question

Using nested formulas or VBA, I want to return ratings that are greater than 0 in each rating column for all individuals. The results for each column should be right below the last filled row as shown below: Spreadsheet before calculations:

     Rating A     Ratings B     Ratings C
Jane    0           -1              0
Rick    1           -2              1
Johnny -1            2              5
James   3            2              3

After:

       Rating A        Ratings B         Ratings C
Jane     0                -1                0
Rick     1                -2                1
Johnny  -1                 2                5 
James    3                 2                3
        Rick            Johnny          Johnny
         1                 2                5
        James            James            James
         3                 2                3
                                          Rick
                                            1

As seen above, Rick and James have Ratings A >0 so the formula outputs the results beginning in the next empty row on Rating A Column. Same technique for Ratings B and C. Please note that this is a much smaller exempt of a larger spreadsheet. I can't post the full spreadsheet that's thousands of rows and columns.

Welcome to SO. For best results I recommend you showing us the code you have tried so far, and specify the part where you're having troubles. — rubentd, Nov 06 '18 at 04:59
I would reconsider the output format and possibly consider a good ole fashion pivot table — urdearboy, Nov 06 '18 at 05:38

Gexas · Answer 1 · 2018-11-06T10:24:06.997

0

First of all, as urdearboy suggested I would consider changing output format a bit and using pivot table. However if it not an option, you can try for...next loop.

Code below will loop thought your example and collect results in a way you want, but I took a liberty to put results in separate columns, since it would be much more versatile for future use.

Sub Check_rating()

Dim x As Long
Dim ws As Worksheet
Dim counterA As Long
Dim counterB As Long
Dim counterC As Long

Set ws = Worksheets("Sheet1")

    For x = 2 To Range("Table1").Rows.Count + 1
        If ws.Cells(x, "B").Value > 0 Then
            counterA = counterA + 1
            ws.Cells(counterA, "F").Value = ws.Cells(x, "A").Value
            ws.Cells(counterA, "G").Value = ws.Cells(x, "B").Value
        End If
        If ws.Cells(x, "C").Value > 0 Then
            counterB = counterB + 1
            ws.Cells(counterB, "H").Value = ws.Cells(x, "A").Value
            ws.Cells(counterB, "I").Value = ws.Cells(x, "C").Value
        End If
        If ws.Cells(x, "D").Value > 0 Then
            counterC = counterC + 1
            ws.Cells(counterC, "J").Value = ws.Cells(x, "A").Value
            ws.Cells(counterC, "K").Value = ws.Cells(x, "D").Value
        End If
    Next x

End Sub

edited Nov 06 '18 at 10:24

answered Nov 06 '18 at 07:01

Gexas

648
4
17

1

Note that row counting variables must be of type `Long` because Excel has more rows than `Integer` can handle! It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. – Pᴇʜ Nov 06 '18 at 07:27
Good point, since my example was only few rows long I used Integer. I'll use your advice in future. – Gexas Nov 06 '18 at 09:31
Thanks for the input. I should add that the full table has thousands of columns are rows so I was expecting the column to iterate through thousands of columns. Is it possible to re-adjust the code for that? – Davis AZ Nov 06 '18 at 15:14
I already adjusted it. Only difference from initial code, that I changed variables (`Dim ... As ...` lines) from `Integer` to `Long` – Gexas Nov 07 '18 at 05:51

Returning cell value if greater than 0

1 Answers1