Check if a value is positive or negative VBA

[OlekOleh]

Hello everyone,

I have been trying to write some VBA code to check if a cell contains a positive, or negative percentage. Based on whether it is positive, or negative I would then like to copy and paste the value into a new table on a separate sheet which has two tables. One containing all the positive values and one with all the negative values.

I wrote a nested if statement prior, but found that my if statement went line by line and would copy over negative values as the false conditions in the positive table. I need to plot this data after it is separated and with all the false conditions being plotted as zeros in my graph it is clustering everything. Although I could easily filter the data I would like to fully automate this process so I enter everything into one sheet and then the graphs are produced on another.

Basically what I need is this:
Current Table (much longer in actual)

Variance %

-63%

41%

24%

-15%

57%

<tbody>
</tbody>

now I need to go through each row in this column and create two tables on a separate sheet that would do this

Positive Variance %

41%

24%

57%

<tbody>
</tbody>

Negative Variance %

-63%

-15%

<tbody>
</tbody>

The main issue with my nested if statement approach is that it does not have intelligence in the sense that once it finds a positive value and moves it to the new sheet it does not recall the last position of that value and starts going through the whole column again basically making duplicate values.

Could use some guidance new to VBA. Thanks for all the help!


Sincerely,
Olek

 

[Joe4]

I think you should just able to use Filters to copy over just the positive data to a new location (and do the same for negative data).
See: http://www.contextures.com/xladvfilter01.html

 

[CalcSux78]

You could use 2 pivot tables with filters for positive and negative values.

 

[OlekOleh]

Will advanced filters update as I add more data as time goes on? I thought it would be locked to a specific data range and that's why I went to VBA.

 

[mikerickson]

AdvancedFIlter was my first thought, but your macro would have to be run every time that new data is added. This could be done automatically with a Change event.

 

[OlekOleh]

I looked up change event but it seems like this is for when existing data is altered in a cell. I will just be adding new data in the future. If I have 100 cells now with variance in a month I might have 200 that would have been added directly after the existing cells. I was thinking of just implementing a for loop that goes into my column and checks if the cell is empty. If it is not empty then an if statement would check the condition of the cell to be either positive or negative. If the value is positive it must be copied and pasted to a new sheet in a new row and column. Now this is where I get confused because if I just leave the code at this the if statement ends and breaks out from the loop. The loop should continue until the end of the row but right now will restart at the beginning. I need a way to tell excel which row and column it has check and continue the loop from that position until the end so duplicates are not made. Any suggestions?

 

[GAdamWTW]

If you don't mind running the macro every time new data is added and you're always in the same workbook, you can do this simply by recording a macro of you filtering, select all (Ctrl+A) then copy/paste on the next sheet.
To avoid specific values in the filtering set the sheet with conditional formatting and filter by the condition or by color. <0 could be Red. >0 Green. =0 Green(Yellow) Then filter. The filter options already have a default option to sort or filter by color.

If working in different workbooks you can still do it this way but it'd be more work to place in the marco to add conditional formatting rules to the workbook.

 

[mikerickson]

If the big list is in Sheet1A1 and the negative values go to Sheet2!A1 and positive to Sheet2!C1,

Putting this code in the code module for Sheet.
Changing the values in the big list on Sheet1, will update the two lists on Sheet2.

Adjust the SourceListRange, rngNegative and rngPositive arguments to suit your needs.

' in Sheet1 code module

Function SourceListRange() As Range
    Set SourceListRange = Me.Range("A1"): Rem adjust
End Function

Sub PosNegLists()
    Dim rngCombinedList As Range
    Dim rngNegative As Range, rngPositive As Range
    Dim critRange As Range
    
    Set rngNegative = Sheet2.Range("A1"): Rem adjust
    Set rngPositive = Sheet2.Range("C1"): Rem adjust
    
    With SourceListRange
        Set rngCombinedList = Range(.Cells(1, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
    End With
    
    With rngNegative.Parent.UsedRange
        With .Cells(1, .Columns.Count + 2)
            Set critRange = .Cells.Resize(2, 1)
        End With
    End With
    
    Application.ScreenUpdating = False
    
    critRange.Cells(1, 1).Value = rngCombinedList.Cells(1, 1).Value ' copy header
    
    critRange.Cells(2, 1).Value = "<0"
    With rngNegative.Cells(1, 1)
       .ClearContents
        rngCombinedList.AdvancedFilter xlFilterCopy, CriteriaRange:=critRange, CopyToRange:=.Cells
        .Value = "Negative " & .Value
    End With
    
    critRange.Cells(2, 1).Value = ">=0"
    With rngPositive.Cells(1, 1)
        .ClearContents
        rngCombinedList.AdvancedFilter xlFilterCopy, CriteriaRange:=critRange, CopyToRange:=.Cells
        .Value = "Postiive " & .Value
    End With

    critRange.Delete
    
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Nothing Is Application.Intersect(Target, SourceListRange.EntireColumn) Then
        Call PosNegLists
    End If
End Sub

The user doesnt have to call the filtering routine, it goes automatically.