CODEWITHSUNDEEP
excelvba
coldpumpkin
coldpumpkin

Reputation: 714

Excel VBA IF greater than or less than not working properly

I'm trying to test if the old value of a cell is greater or less than the current value being inserted. However, it keeps saying it's neither.

I really have no idea why... Can anyone help me out?

Dim oldCellValue As Integer
Dim curSheetName As String
Dim curCellAddress As String
Dim curCellValue As Integer

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
    oldCellValue = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    curSheetName = ActiveSheet.Name
    curCellAddress = ActiveCell.Offset(-1, 0).Address
    curCellValue = ActiveCell.Offset(-1, 0).Value

    If oldCellValue = 0 And curCellValue = 0 Then
        Exit Sub
    Else

        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
        End With

        Set Workbook = Workbooks.Open("stock.xlsx")

        If oldCellValue > curCellValue Then
            Sheets(curSheetName).Range(curCellAddress) = ActiveCell.Value + (oldCellValue - curCellValue)
        ElseIf curCellValue < oldCellValue Then
            Sheets(curSheetName).Range(curCellAddress) = ActiveCell.Value - (curCellValue - oldCellValue)
        Else
            MsgBox "Neither"
        End If

        Workbook.Save
        Workbook.Close

        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
        End With

    End If

End Sub

EDIT: I've updated the code with the suggestions and the fix, even though there's a new problem. See below comments.

Dim oldCellValue As Integer
Dim curSheetName As String
Dim curCellAddress As String
Dim curCellValue As Integer

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
    oldCellValue = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    curSheetName = ActiveSheet.Name
    curCellAddress = Target.Address
    curCellValue = Target.Value

    If oldCellValue = 0 And curCellValue = 0 Or oldCellValue = curCellValue Then
        Exit Sub
    Else

        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
        End With

        Set Workbook = Workbooks.Open("stock.xlsx")

        If oldCellValue > curCellValue Then
            Sheets(curSheetName).Range(curCellAddress) = ActiveCell.Value + (oldCellValue - curCellValue)
            MsgBox ActiveCell.Value
        Else
            Sheets(curSheetName).Range(curCellAddress) = ActiveCell.Value - (curCellValue - oldCellValue)
            MsgBox ActiveCell.Value
        End If

        Workbook.Save
        Workbook.Close

        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
        End With

    End If

End Sub

Upvotes: 0

Views: 4966

Answers (1)

cyboashu
cyboashu

Reputation: 10433

oldCellValue > curCellValue and curCellValue < oldCellValue are technically the same conditions.

if oldcell value= 10 and New cell value =11

then oldCellValue > curCellValue =False (10 >11)

and so is

curCellValue < oldCellValue = False (11 < 10)

Change curCellValue < oldCellValue to curCellValue > oldCellValue

Upvotes: 2

Related Questions