Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
If(A1=Y,ok,"")
Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
VBA doesn't care whether a value is a constant or the result of a
formula: If Range("B1").Value = "OK" Then <whatever you're trying to do here End If However, a couple of ideas: 1) VBA comparisons are case sensitive. The above line will execute if B1 = "OK", but not "Ok", "ok", "oK". You could try: If LCase(Range("B1").Value) = "ok" Then 2) Your listed 'formula', if preceded by an "=" sign, is expecting named values, Y and ok, rather than text literals (since they're not in quotes). What are those values? In article , Jock wrote: If(A1=Y,ok,"") Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
Always a good idea to post any code you have, that way we would know what it
is you are trying to accomplish with it. It is hard to tell what result you want to have happen. Here is code for one cell (B1)... you can either expand on it or come back to the newsgroup and tell us what you actually want to happen when you find a cell with the letters "ok" in them. If StrComp(Range("B1").Value, "ok", vbTextCompare) = 0 Then If Range("B1").HasFormula Then MsgBox "B1 has a formula and the letters ""OK"" in it." Else MsgBox "B1 has ""OK"" in it, but no formula." End If End If -- Rick (MVP - Excel) "Jock" wrote in message ... If(A1=Y,ok,"") Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
Hi Rick,
As a basic scenario, cells in A, B and C are 'summary' cells and have formulae in them which are date driven and will display "W" in A, "Str" in B and "Sty" in C when certain conditions are met elsewhere in the sheet. So when something does appear in any or all of A, B, C in any row, I want the date that it appeared to be placed on the same row but offset by (0, 44). This works (using the Worksheet_Change code below) when "W" for instance is placed in A. However, when "W" appears as the result of a formula, nothing happens. code: On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A4:C10000")) Is Nothing Then With Target If .Value < "" Then .Offset(0, 44).Value = Format(Date, "dd/mmm") End If End With End If How can this be adapted to act when something appears in A:C to place the date 44 cells along? Could a simple formula be used in the cells 44 along rather than use code? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: Always a good idea to post any code you have, that way we would know what it is you are trying to accomplish with it. It is hard to tell what result you want to have happen. Here is code for one cell (B1)... you can either expand on it or come back to the newsgroup and tell us what you actually want to happen when you find a cell with the letters "ok" in them. If StrComp(Range("B1").Value, "ok", vbTextCompare) = 0 Then If Range("B1").HasFormula Then MsgBox "B1 has a formula and the letters ""OK"" in it." Else MsgBox "B1 has ""OK"" in it, but no formula." End If End If -- Rick (MVP - Excel) "Jock" wrote in message ... If(A1=Y,ok,"") Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
Formula changes do not generate Change events... the cells they are
dependent on do that. There is a way to program for that, but the layout of your worksheet will dictate how to approach it. Let me give you a simple example and then show you where the complication could come in... hopefully this will be enough for you to see how to apply it to your particular layout. On a new worksheet, put this formula in D3... =IF(A1="","Nothing","Something") Now put this code in that worksheet's code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next 'Needed for when there are no dependents If Not Intersect(Target.Dependents, Range("D1:D10")) Is Nothing Then Target.Dependents.Offset(0, 1).Value = Now End If End Sub Go back to the worksheet and change the value in A1. When you do that, the date/time is placed in the column next to the formula that refers to A1. This seems to do what you asked. Now, for the complication. Erase the date/time that was placed in E3 and add this formula in D5... =A1<"" Now, there are two different formulas referencing A1. Change the value in A1. Notice that date/times are now placed next to **both** formulas. This is what I meant by needing to know your layout... if you have this multiple reference to the same source cell, you might need additional filtering code of some kind to lock down which cell gets the date/time place in it (I'm thinking this would be data/structural layout dependent). Anyway, I think the Dependents property of the Target range is what you will need to work with... hopefully the above has been of some help. -- Rick (MVP - Excel) "Jock" wrote in message ... Hi Rick, As a basic scenario, cells in A, B and C are 'summary' cells and have formulae in them which are date driven and will display "W" in A, "Str" in B and "Sty" in C when certain conditions are met elsewhere in the sheet. So when something does appear in any or all of A, B, C in any row, I want the date that it appeared to be placed on the same row but offset by (0, 44). This works (using the Worksheet_Change code below) when "W" for instance is placed in A. However, when "W" appears as the result of a formula, nothing happens. code: On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A4:C10000")) Is Nothing Then With Target If .Value < "" Then .Offset(0, 44).Value = Format(Date, "dd/mmm") End If End With End If How can this be adapted to act when something appears in A:C to place the date 44 cells along? Could a simple formula be used in the cells 44 along rather than use code? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: Always a good idea to post any code you have, that way we would know what it is you are trying to accomplish with it. It is hard to tell what result you want to have happen. Here is code for one cell (B1)... you can either expand on it or come back to the newsgroup and tell us what you actually want to happen when you find a cell with the letters "ok" in them. If StrComp(Range("B1").Value, "ok", vbTextCompare) = 0 Then If Range("B1").HasFormula Then MsgBox "B1 has a formula and the letters ""OK"" in it." Else MsgBox "B1 has ""OK"" in it, but no formula." End If End If -- Rick (MVP - Excel) "Jock" wrote in message ... If(A1=Y,ok,"") Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks -- Traa Dy Liooar Jock |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
Rick, thanks for putting the effort in here - much appreciated. I've just got back after a few days off for an endurance race. I haven't had a chance to implement your ideas but I will and I'll get back to you. Thanks again Jock -- Traa Dy Liooar Jock "Rick Rothstein" wrote: Formula changes do not generate Change events... the cells they are dependent on do that. There is a way to program for that, but the layout of your worksheet will dictate how to approach it. Let me give you a simple example and then show you where the complication could come in... hopefully this will be enough for you to see how to apply it to your particular layout. On a new worksheet, put this formula in D3... =IF(A1="","Nothing","Something") Now put this code in that worksheet's code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next 'Needed for when there are no dependents If Not Intersect(Target.Dependents, Range("D1:D10")) Is Nothing Then Target.Dependents.Offset(0, 1).Value = Now End If End Sub Go back to the worksheet and change the value in A1. When you do that, the date/time is placed in the column next to the formula that refers to A1. This seems to do what you asked. Now, for the complication. Erase the date/time that was placed in E3 and add this formula in D5... =A1<"" Now, there are two different formulas referencing A1. Change the value in A1. Notice that date/times are now placed next to **both** formulas. This is what I meant by needing to know your layout... if you have this multiple reference to the same source cell, you might need additional filtering code of some kind to lock down which cell gets the date/time place in it (I'm thinking this would be data/structural layout dependent). Anyway, I think the Dependents property of the Target range is what you will need to work with... hopefully the above has been of some help. -- Rick (MVP - Excel) "Jock" wrote in message ... Hi Rick, As a basic scenario, cells in A, B and C are 'summary' cells and have formulae in them which are date driven and will display "W" in A, "Str" in B and "Sty" in C when certain conditions are met elsewhere in the sheet. So when something does appear in any or all of A, B, C in any row, I want the date that it appeared to be placed on the same row but offset by (0, 44). This works (using the Worksheet_Change code below) when "W" for instance is placed in A. However, when "W" appears as the result of a formula, nothing happens. code: On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A4:C10000")) Is Nothing Then With Target If .Value < "" Then .Offset(0, 44).Value = Format(Date, "dd/mmm") End If End With End If How can this be adapted to act when something appears in A:C to place the date 44 cells along? Could a simple formula be used in the cells 44 along rather than use code? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: Always a good idea to post any code you have, that way we would know what it is you are trying to accomplish with it. It is hard to tell what result you want to have happen. Here is code for one cell (B1)... you can either expand on it or come back to the newsgroup and tell us what you actually want to happen when you find a cell with the letters "ok" in them. If StrComp(Range("B1").Value, "ok", vbTextCompare) = 0 Then If Range("B1").HasFormula Then MsgBox "B1 has a formula and the letters ""OK"" in it." Else MsgBox "B1 has ""OK"" in it, but no formula." End If End If -- Rick (MVP - Excel) "Jock" wrote in message ... If(A1=Y,ok,"") Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks -- Traa Dy Liooar Jock |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA referencing the result of a formula
Hi Rick, I've had a play with the examples now. What you've put will work well when there is a 'manual' cell change. I was looking for something which will work without any user input at all. So for instance in A1 would be; =IF(B1<now(),W,"") What I was hoping for is when the current date was greater than the (manually entered) date in B1, a "W" would appear in C1. That's straightforward enough but when a "W" does appear (ie upon refresh or worksheet open), without user interaction, can the date appear then in D1? It might sound a bit pointless but the longer story is that I want to write some code which will look at the dates in D and if they are between certain parameters, then copy the entire row(s) to another sheet. This would be done at WorksheetOpen. Thanks -- Traa Dy Liooar Jock "Rick Rothstein" wrote: Formula changes do not generate Change events... the cells they are dependent on do that. There is a way to program for that, but the layout of your worksheet will dictate how to approach it. Let me give you a simple example and then show you where the complication could come in... hopefully this will be enough for you to see how to apply it to your particular layout. On a new worksheet, put this formula in D3... =IF(A1="","Nothing","Something") Now put this code in that worksheet's code window... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next 'Needed for when there are no dependents If Not Intersect(Target.Dependents, Range("D1:D10")) Is Nothing Then Target.Dependents.Offset(0, 1).Value = Now End If End Sub Go back to the worksheet and change the value in A1. When you do that, the date/time is placed in the column next to the formula that refers to A1. This seems to do what you asked. Now, for the complication. Erase the date/time that was placed in E3 and add this formula in D5... =A1<"" Now, there are two different formulas referencing A1. Change the value in A1. Notice that date/times are now placed next to **both** formulas. This is what I meant by needing to know your layout... if you have this multiple reference to the same source cell, you might need additional filtering code of some kind to lock down which cell gets the date/time place in it (I'm thinking this would be data/structural layout dependent). Anyway, I think the Dependents property of the Target range is what you will need to work with... hopefully the above has been of some help. -- Rick (MVP - Excel) "Jock" wrote in message ... Hi Rick, As a basic scenario, cells in A, B and C are 'summary' cells and have formulae in them which are date driven and will display "W" in A, "Str" in B and "Sty" in C when certain conditions are met elsewhere in the sheet. So when something does appear in any or all of A, B, C in any row, I want the date that it appeared to be placed on the same row but offset by (0, 44). This works (using the Worksheet_Change code below) when "W" for instance is placed in A. However, when "W" appears as the result of a formula, nothing happens. code: On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A4:C10000")) Is Nothing Then With Target If .Value < "" Then .Offset(0, 44).Value = Format(Date, "dd/mmm") End If End With End If How can this be adapted to act when something appears in A:C to place the date 44 cells along? Could a simple formula be used in the cells 44 along rather than use code? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: Always a good idea to post any code you have, that way we would know what it is you are trying to accomplish with it. It is hard to tell what result you want to have happen. Here is code for one cell (B1)... you can either expand on it or come back to the newsgroup and tell us what you actually want to happen when you find a cell with the letters "ok" in them. If StrComp(Range("B1").Value, "ok", vbTextCompare) = 0 Then If Range("B1").HasFormula Then MsgBox "B1 has a formula and the letters ""OK"" in it." Else MsgBox "B1 has ""OK"" in it, but no formula." End If End If -- Rick (MVP - Excel) "Jock" wrote in message ... If(A1=Y,ok,"") Is it possible for code to reference column "B" (where this formula is) and detect an 'ok' which has not been manually input? The code I have works fine when 'ok' is entered but not when it is the result of a formula. Thanks -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Bar F9 Result differs from cell result??? | Excel Worksheet Functions | |||
Referencing a different tab according to a formula result | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
need help referencing cell using result of formula | Excel Discussion (Misc queries) | |||
referencing autofilter result | Excel Discussion (Misc queries) |