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 wrote:
> 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
>
>
>
>
>
>

[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



On Tue, 06 Dec 2005 09:17:29 +1100, Rowan Drummond <rowanzsaNotThis@hotmail.com> wrote:

>Why not use a formula. In C1 enter:
>=if(b1="","",A1-B1)
>and copy down.
>
>Hope this helps
>Rowan
>
>Susan Hayes wrote:
>> 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
>>
>>
>>
>>
>>
>>

[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

--


Gary


"Susan Hayes" <asdf@hotmail.com> wrote in message
news:mhhfp1hkar8e1f4hjdbe652okh1ltg4huk@4ax.com...
> 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
>
>
>
> On Tue, 06 Dec 2005 09:17:29 +1100, Rowan Drummond
> <rowanzsaNotThis@hotmail.com> wrote:
>
>>Why not use a formula. In C1 enter:
>>=if(b1="","",A1-B1)
>>and copy down.
>>
>>Hope this helps
>>Rowan
>>
>>Susan Hayes wrote:
>>> 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]

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

Susan Hayes wrote:
> 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
>
>
>
> On Tue, 06 Dec 2005 09:17:29 +1100, Rowan Drummond <rowanzsaNotThis@hotmail.com> wrote:
>
>
>>Why not use a formula. In C1 enter:
>>=if(b1="","",A1-B1)
>>and copy down.
>>
>>Hope this helps
>>Rowan
>>
>>Susan Hayes wrote:
>>
>>>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
>>>
>>>
>>>
>>>
>>>
>>>
>
>

[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

--


Gary


"Susan Hayes" <asdf@hotmail.com> wrote in message
news:mhhfp1hkar8e1f4hjdbe652okh1ltg4huk@4ax.com...
> 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
>
>
>
> On Tue, 06 Dec 2005 09:17:29 +1100, Rowan Drummond
> <rowanzsaNotThis@hotmail.com> wrote:
>
>>Why not use a formula. In C1 enter:
>>=if(b1="","",A1-B1)
>>and copy down.
>>
>>Hope this helps
>>Rowan
>>
>>Susan Hayes wrote:
>>> 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
>>>
>>>
>>>
>>>
>>>
>>>
>