Programmatically counting info from formula
 

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt

Programmatically counting info from formula
 

Turn on the macro recorder, then do
Edit=Find
and search for you sheet name. Specify to look in formulas.

Now turn off the macro recorder.

This is an easy way to get the parameters you need for the find command.

Now go to VBA and use the help on the FindNext command. Look at the visual
basic example. It shows how to find all instances. Combine that with the
code you recorded.

Now just add a check for the value of the cell being red, yellow or green.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt

Programmatically counting info from formula
 

I'm not sure this is going to get me exactly what I want. I want to check
the formulas as follows:

Cell I5 formula should contain Sheet1
Cell I6 formula should contain Sheet2
Cell I7 formula should contain Sheet3

Etc. The sheet names will be different in my case, but this gives an
example of what I want.

The other will definitely be useful in the future though.
"Tom Ogilvy" wrote:

Turn on the macro recorder, then do
Edit=Find
and search for you sheet name. Specify to look in formulas.

Now turn off the macro recorder.

This is an easy way to get the parameters you need for the find command.

Now go to VBA and use the help on the FindNext command. Look at the visual
basic example. It shows how to find all instances. Combine that with the
code you recorded.

Now just add a check for the value of the cell being red, yellow or green.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt

Programmatically counting info from formula
 

I am assuming that you want to count cells like:

=IF(Sheet1!B2=0,"RED","PURPLE") that display RED, etc.

The following code will count such cells:

Sub Macro1()
Dim r As Range
Dim count As Long
Dim s1 As String
Dim s2 As String

count = 0
s2 = "Sheet1"

For Each r In Selection
If r.Value = "RED" Or r.Value = "GREEN" Or r.Value = "YELLOW" Then
s1 = r.Formula
If InStr(1, s1, s2) 0 Then
count = count + 1
End If
End If
Next
MsgBox (count)
End Sub


On the worksheet, just select the cells you want to check and run the macro.
--
Gary's Student


"Barb Reinhardt" wrote:

I'm not sure this is going to get me exactly what I want. I want to check
the formulas as follows:

Cell I5 formula should contain Sheet1
Cell I6 formula should contain Sheet2
Cell I7 formula should contain Sheet3

Etc. The sheet names will be different in my case, but this gives an
example of what I want.

The other will definitely be useful in the future though.
"Tom Ogilvy" wrote:

Turn on the macro recorder, then do
Edit=Find
and search for you sheet name. Specify to look in formulas.

Now turn off the macro recorder.

This is an easy way to get the parameters you need for the find command.

Now go to VBA and use the help on the FindNext command. Look at the visual
basic example. It shows how to find all instances. Combine that with the
code you recorded.

Now just add a check for the value of the cell being red, yellow or green.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt

Programmatically counting info from formula
 

I don't know, only you can decide:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 05/04/2006 by Barb Reinhardt
'

'
' Selection.Find(What:="sheet1", After:=ActiveCell, LookIn:=xlFormulas, _
' LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
' MatchCase:=False, SearchFormat:=False).Activate
Dim firstaddress as String, c as Range
Dim rng as Range, s as String
With Worksheets("Summary").Cells
'With Worksheets(1).Range("a1:a500")
'Set c = .Find(2, LookIn:=xlValues)
Set c = .Find(What:="sheet1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
' c.Value = 5
s = LCase(c.Value)
Select Case s
Case "red", "green", "yellow"
If rng Is Nothing Then
Set rng = rng
Else
Set rng = Union(rng, c)
End If
End Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
If Not rng Is Nothing Then
rng.Select
End If

End With

End Sub


--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote:

I'm not sure this is going to get me exactly what I want. I want to check
the formulas as follows:

Cell I5 formula should contain Sheet1
Cell I6 formula should contain Sheet2
Cell I7 formula should contain Sheet3

Etc. The sheet names will be different in my case, but this gives an
example of what I want.

The other will definitely be useful in the future though.
"Tom Ogilvy" wrote:

Turn on the macro recorder, then do
Edit=Find
and search for you sheet name. Specify to look in formulas.

Now turn off the macro recorder.

This is an easy way to get the parameters you need for the find command.

Now go to VBA and use the help on the FindNext command. Look at the visual
basic example. It shows how to find all instances. Combine that with the
code you recorded.

Now just add a check for the value of the cell being red, yellow or green.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt

Programmatically counting info from formula
 

Left off the punch line

If Not rng Is Nothing Then
rng.Select
msgbox "Count is " & rng.count
Else
msgbox "Count is " & 0
End If

or slowly loop through all the cells as suggested by others.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

I don't know, only you can decide:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 05/04/2006 by Barb Reinhardt
'

'
' Selection.Find(What:="sheet1", After:=ActiveCell, LookIn:=xlFormulas, _
' LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
' MatchCase:=False, SearchFormat:=False).Activate
Dim firstaddress as String, c as Range
Dim rng as Range, s as String
With Worksheets("Summary").Cells
'With Worksheets(1).Range("a1:a500")
'Set c = .Find(2, LookIn:=xlValues)
Set c = .Find(What:="sheet1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
' c.Value = 5
s = LCase(c.Value)
Select Case s
Case "red", "green", "yellow"
If rng Is Nothing Then
Set rng = rng
Else
Set rng = Union(rng, c)
End If
End Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
If Not rng Is Nothing Then
rng.Select
End If

End With

End Sub


--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote:

I'm not sure this is going to get me exactly what I want. I want to check
the formulas as follows:

Cell I5 formula should contain Sheet1
Cell I6 formula should contain Sheet2
Cell I7 formula should contain Sheet3

Etc. The sheet names will be different in my case, but this gives an
example of what I want.

The other will definitely be useful in the future though.
"Tom Ogilvy" wrote:

Turn on the macro recorder, then do
Edit=Find
and search for you sheet name. Specify to look in formulas.

Now turn off the macro recorder.

This is an easy way to get the parameters you need for the find command.

Now go to VBA and use the help on the FindNext command. Look at the visual
basic example. It shows how to find all instances. Combine that with the
code you recorded.

Now just add a check for the value of the cell being red, yellow or green.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt