VBA referencing the result of a formula

  • Thread starter Thread starter Jock
  • Start date Start date
J

Jock

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
 
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?
 
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
 
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 said:
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
Click to expand...
 
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 said:
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?
Click to expand...
 
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
 
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
 
Back
Top