Loop: Do Until Fill Color is 16 Debug Error

[ryger48]

Hello everyone,

I am familiar with a few coding languages, including PHP, C++, etc, however I am new to Excel and VBA. I am attempting to create a do-while/do-until loop which stays in the same column, and keeps descending row to row until it hits a Gray-filled cell, at which point it takes the value of that cell and returns it. The data looks something like this:

Sam	4	7	34	4	3	5	8
	5	4	44	34	5	8	5
	3	34	22	34	5	68	85
Mark	234	43	23	65	345	23	434
	234	234	65	45	3	45	65
	745	4	5	63	345	4	3

<tbody>
</tbody>

(It was easier in this post to change the font color instead of the fill color, but you get the idea). Also, the number of rows between names varies. What I have written so far is this:

Function checkFill()


Dim i As Integer
Dim AHT As Integer


i = 0


Do While Cells(i, 5).Interior.ColorIndex > 0
    If Cells(i, 5).Interior.ColorIndex = 16 Then
        AHT = Cells(i, 5).Value
        Exit Do
    End If
    i = i + 1
Loop


End Function

Should I use a function here, or sub? Also, I keep getting an "Application-defined or object-defined error" message. What am I doing wrong/is there an easier way?

Thanks in advance for any help with this, I greatly appreciate it!

 

[Peter_SSs]

Welcome to the MrExcel board!

1. You are getting the error with the following line ..

Do While Cells(i, 5).Interior.ColorIndex > 0

.. because the first time the code gets to that line i=0. That is, you are trying to check the colour of a cell in row 0, which doesn't exist.
So you could set i=1 to start to solve that.

2. If your non-gray cells have no fill then your code will finish prematurely because the interior.colorindex will return -4142 for a non-filled cell.
There could be various ways to fix that, also depending on the next piont.

3. Is it special that the cells are gray or are you really just looking for the last number in column E before the second name (Mark in your example)?
I ask because there may be a simpler way than checking each cell's colour.

 

[ryger48]

Thank you for your response!

1. Ah, that makes sense. I must have been thinking the 0 would be the first in line, like in an array, etc.

2. I changed the code to "Do Until Cells(i, 5).Interior.ColorIndex = 16" to get around that, now the code is compiling.

3. Yes, that's correct. In the reports I am getting, the gray sections are always the last line for each agent and contain a particular number, in this case an average handle time, for that agent. But because the number of rows differs between agents, based on how many calls they've taken, I wasn't sure how else to zero in on their average handle time. If there is a simpler way, I would love to employ it.

 

[Peter_SSs]

3. Yes, that's correct. In the reports I am getting, the gray sections are always the last line for each agent and contain a particular number, in this case an average handle time, for that agent. But because the number of rows differs between agents, based on how many calls they've taken, I wasn't sure how else to zero in on their average handle time. If there is a simpler way, I would love to employ it.

Whether you use a Function or a Sub depends a bit on just how/where you want to use this and also personal choice.

I've used a function since that is how you started out. Using a Function, also allows it to be used in the worksheet as I've shown with a few examples in column K below.

Function FindVal(Agent As String, Optional Col As String = "E", Optional NameCol As String = "A")
  Dim myRow As Long, LastRow As Long
  Dim AgentStart As Range
  
  Set AgentStart = Columns(NameCol).Find(What:=Agent, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  If AgentStart Is Nothing Then
    FindVal = "Agent not found"
  Else
    LastRow = Cells(Rows.Count, Col).End(xlUp).Row
    myRow = Columns(NameCol).Find(What:="*", After:=AgentStart, _
      LookIn:=xlValues, SearchDirection:=xlNext, SearchFormat:=False).Row - 1
    If myRow = 0 Then myRow = LastRow
    FindVal = Cells(myRow, Col).Value
  End If
End Function

The Function can then be used in code like this ..

Sub Test()
  Dim x As Variant
  
  x = FindVal("Sam")
  
'  Other examples:
'  x = FindVal("Mark", "F")
'  x = FindVal("Sam", "B", "A")
'  x = FindVal("Tom")

  MsgBox x
  
End Sub

.. or in the worksheet like this ..

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Val 1	Val 2	Val 3	Val 4	Val 5	Val 6	Val 7			34
2	Sam	4	7	34	4	3	5	8			345
3		5	4	44	34	5	8	5			Agent not found
4		3	34	22	34	5	68	85
5	Mark	234	43	23	65	345	23	434
6		234	234	65	45	3	45	65
7		745	4	5	63	345	4	3
8
Find value

 

[ryger48]

I have had the opportunity to test what you wrote, and it works beautifully! Thank you SO much!

 

[Peter_SSs]

I have had the opportunity to test what you wrote, and it works beautifully! Thank you SO much!

Cheers, glad it worked for you.
A lot simpler than cycling through looking for a cell colour!