Comparing values in cell B to cell A

[Susan Hayes]

Hello

What I want to do:
If any row in col B is empty do nothing, but if a number is entered in any row of col B, immediately calculate a
difference of what is in column A to B

What Im trying to do: (if this is the right thinking)
Im trying to read the address of B and make a calculation (the difference) by reading the value from col A in the same
row, but not sure exactly how to reference col A after reading col B:
User enters value in col b, the number is read, and somehow the address is read in col A, and then value is read in A
and difference is taken.

Any help is greatly appreciated

Susan


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("b1..b1000")) Is Nothing Then
With Target

Dim rng As Range
Set rng = ActiveCell
Dim generalvalue

Dim generalrng As Range
generalrng = rng(0, 0)
genralvalue = generalrng.Value

If .Value < generalvalue Then '.Address(0, 1) Then
.value<generalvalue

End If

End With
End If
ws_exit:
Application.EnableEvents = True

End Sub

 

[Rowan Drummond]

Why not use a formula. In C1 enter:
=if(b1="","",A1-B1)
and copy down.

Hope this helps
Rowan

 

[Susan Hayes]

Hi Rowan - Thanks for your response

Yes that would work but Im looking to include this in a much larger program in vba
If you were to program it in vba would you follow this method?
Thanks
Susan

 

[Gary Keramidas]

not sure i understand exactly, but give this a try. just right click the
sheetname, choose view code and paste it there


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
If Range("b" & Target.Row) Is Nothing Then
Exit Sub
Else
Range("b" & Target.Row).Value = Range("a" & Target.Row).Value - _
Range("b" & Target.Row).Value
End If
End If
End If
End Sub

 

[Rowan Drummond]

Hi Susan

It's difficult for me to say what method I would follow without knowing
anything about your project but if for example I was formatting a sheet
and wanted to have this functionality in the column I might add the
formula to the required range eg:

Range("C1:C1000").FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-2]+RC[-1])"

If you wanted to use the worksheet change event then that would be
something like:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

Application.EnableEvents = False
On Error GoTo ws_exit

If Not Intersect(Target, Range("B1:B1000")) Is Nothing Then
For Each cell In Target
If cell.Column = 2 And IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = _
cell.Offset(0, -1).Value + cell.Value
End If
Next cell
End If
ws_exit:
Application.EnableEvents = True

End Sub

Hope this helps
Rowan

 

[Gary Keramidas]

in case you only want it to function if column A is >0

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
If Range("b" & Target.Row) Is Nothing Then
Exit Sub
Else
If Range("a" & Target.Row).Value > 0 Then
Range("b" & Target.Row).Value = Range("a" & Target.Row).Value -
_
Range("b" & Target.Row).Value
End If
End If
End If
End If
End Sub