For EACH cell in specific range meeting specific criteria, call a sub

[ks100]

Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.

Code in plain english would look like this:

For EACH cell in range A1:BZ500 meeting the following criteria:

Cell value is a date

AND

Cell's date is at least a week or more in the future

AND

Cell background (Fill) = RGB color code: (R:191 G:191 B:191)


DO the following:


Call repeatingsub

[Richard Buttrey]

Hi,

Are you sure you understand the implications of looping through 39000 cells or was that a typo?

[ks100]

Hi,

Are you sure you understand the implications of looping through 39000 cells or was that a typo?

Well is there a way to define a range as the last row and column used on the VISIBLE worksheet?

The entire worksheet has 39k cells, however only a few thousands are actually visible (based on applied filters).

[6StringJazzer]

   Dim C As Range
   
   For Each C In Range("A1:BZ500")
   
      If IsDate(C.Value) And C.Value > Date + 7 And C.Interior.Color = RGB(191, 191, 191) Then
         repeatingsub
      End If
      
   Next C

[ks100]

   Dim C As Range
   
   For Each C In Range("A1:BZ500")
   
      If IsDate(C.Value) And C.Value > Date + 7 And C.Interior.Color = RGB(191, 191, 191) Then
         repeatingsub
      End If
      
   Next C

I tried the following:

 Sub test()
 
 
 
 Dim C As Range
   
   For Each C In Range("a1:aw250")
   
      If IsDate(C.Value) And C.Value > Date + 7 And C.Interior.Color = RGB(191, 191, 191) Then
         
         Dim testmsg As String
         testmsg = ActiveCell.Value
         
        
         MsgBox testmsg
         
      End If
      
   Next C
End Sub

And it hangs on the same cell (with a date from over a year ago, which does not meet the criteria of being a week or more in the future) , displaying its value in a message box over and over without moving on to the next cell. What am I doing wrong?

[Richard Buttrey]

What does the repeating sub actually do?

Knowing that might determine whether you need a looping procedure at all.

[ks100]

What does the repeating sub actually do?

Knowing that might determine whether you need a looping procedure at all.

I haven't written it yet, but basically it's going to use each identified cell as a reference point, capturing it's column and row as seperate variables, and using them for a few other FOR EACH and DO UNTIL routines.

The end result is populating a bunch of outlook appointments on a calendar.


So basically once it finds a cell meeting the criteria it would save variables:

graydatecol
graydaterow

and then do a bunch of stuff with those variables. when a new gray date cell is found, the variables reset but the same sub is run (making a new outlook appointment).

[6StringJazzer]

The For loop is cycling through each cell in the Range. But it does not make that cell active. Your MsgBox call uses ActiveCell (which I discourage in most cases), and ActiveCell never changes. The code is not getting hung up on one cell, just the message is.

Try this:

 Sub test()
 
 
 
 Dim C As Range
   
   For Each C In Range("a1:aw250")
   
      If IsDate(C.Value) And C.Value > Date + 7 And C.Interior.Color = RGB(191, 191, 191) Then
         
          MsgBox "Value of cell " & C.Address & ": " & C.Value
         
      End If
      
   Next C
End Sub

[ks100]

The For loop is cycling through each cell in the Range. But it does not make that cell active. Your MsgBox call uses ActiveCell (which I discourage in most cases), and ActiveCell never changes. The code is not getting hung up on one cell, just the message is.

Try this:

 Sub test()
 
 
 
 Dim C As Range
   
   For Each C In Range("a1:aw250")
   
      If IsDate(C.Value) And C.Value > Date + 7 And C.Interior.Color = RGB(191, 191, 191) Then
         
          MsgBox "Value of cell " & C.Address & ": " & C.Value
         
      End If
      
   Next C
End Sub

The msgbox pulling the value of the activecell was a neccesary test since the sub that will actually be called runs entirely off activecell commands. So in order for the next sub to work, I need to be able to reference whatever the active c is as "activecell".

for example, I will be doing such things as:

"Once C is found, save the column number it's in as variable X and the row number as variable Y"

Then I would run other tasks based on it's row and column (basically pulling other sets of data from the same row/column)

Will this script allow that to happen?

[6StringJazzer]

First, it is not necessary to quote every post when responding. Usually a quote is not necessary at all; include a quote if you are not responding to the most recent post in the thread, or if you want to highlight something specific that you are responding to.

...the sub that will actually be called runs entirely off activecell commands.

This is not a best practice. (This is what is known as "common environment coupling".) It is undesirable for exactly the reason you see here. The fact that your sub depends on ActiveCell is undocumented and unenforceable. The repeating sub has no control over or visibility to what cell is active at any given time. Future changes in either sub that affect ActiveCell have the potential to introduce bugs that are very difficult to diagnose. I recommend that you add a single parameter to your sub to give the range of the cell to act on, and pass in C when you call it.

If you absolutely must use ActiveCell then you have to activate the cell before calling:

C.Activate
repeatingsub

[ks100]

I would love to know a more efficient/stable way. I'll post a sample workbook shortly.

[ks100]

The image below is a very simplified sample of the spreadsheet I am working with and what I want to accomplish. I can figure out most of the code myself, but I am wondering what the best way to accomplish all this is, as the only way I know how to do it is with activecell.

I am not expecting the code to be written for me, but I was hoping someone could point me in the right direction as far as showing me what function to use to accomplish the capturing of task, person and department names, and a brief example of how it works.



example72.jpg