Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in cell B to cell A
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in cell B to cell A
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in cell B to cell A
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in cell B to cell A
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" wrote in message ... 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in cell B to cell A
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing values in cell B to cell A
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" wrote in message ... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting: comparing cell values | Excel Worksheet Functions | |||
Question about comparing 2 cell values | Excel Discussion (Misc queries) | |||
Comparing multiple cell values | Excel Discussion (Misc queries) | |||
Counting values and comparing them to a single cell | Excel Discussion (Misc queries) | |||
Comparing cell values | Excel Programming |