PDA

View Full Version : Problems using For Loops and Find function



BeachBum
08-10-2016, 09:21 PM
Hi all,

I have been trying to create a section of code that will go through a data set, find all the entries in the first column that are the same as the first row and put them in an array. It will then calculate the maximum and minimum of this range and subtract one from the other. The result plus 1 will be added to the rows corresponding to that ID in a new column (ie saying how many sessions it occurs in. This could also be done using a count of the new array). The code then moves onto the next row in the data set and repeats. This uses the FindAll function.

The attached spreadsheet contains the data set and code.

I have written the following code:

Sub DurationOfStay()
Dim ic As Long
Dim Celr As Range
Dim vMin, vMax
Dim xc
Dim FoundCells As Range
Dim Z As Long
Dim fnd As String, FirstFound As String
Dim sessTemp(2, 1) ' These cells are input by the user using another subroutine,
' See cells O4:P6 in "Raw Data"
sessTemp(0, 0) = "8:30"
sessTemp(0, 1) = "11:30"
sessTemp(1, 0) = "11:30"
sessTemp(1, 1) = "14:30"
sessTemp(2, 0) = "14:30"
sessTemp(2, 1) = "17:30"

Dim LRow As Long, LCol As Long
'Determine the data range you want to pivot
LRow = Cells(Rows.Count, 1).End(xlUp).Row
LCol = Cells(1, Columns.Count).End(xlToLeft).Column
'Insert new column to enter the data into
With Worksheets("Raw Data")
.Range("F:F").Insert
.Range("F1").Value = "Duration"
.Range("F:F").NumberFormat = "###"
' Find all the cells in Column A matching A2,
' then find all the cells matching A3,
' then find all the cells matching A4....
For iz = 2 To Cells(Rows.Count, "A").End(xlUp)
FindCell = Worksheets("Raw Data").Cells("A", iz)
FoundCells = FindAll(what:=FindCell)
' Find the min value of Captured Session in the list of same id
vMin = Application.WorksheetFunction.Min(FoundCells)
' Find the max value of Captured Session in the list of same id
vMax = Application.WorksheetFunction.Max(FoundCells)
' if max - min is x then x+1 is added to new column titled "Duration"
For Z = 1 To UBound(sessTemp)
xc = Z - 1
If vMax - vMin = xc Then
Cel.Offset(, 4) = Z
Next
' Move to next row in the Column
If FoundCells.Address = FirstFound Then Next iz

End If
Next
End With
End Sub


Unfortunately It is not recognising that the function FindAll is a vba function. Can anyone tell me what is wrong here or what improvements I could make for efficiency?

Thanks in advance.

Kenneth Hobs
08-11-2016, 06:09 AM
FindAll() is not a VBA built-in routine. I have a routine, and know of two others. I recommend Chip Pearson's method. http://www.cpearson.com/Excel/FindAll.aspx

Once you get Chip's Module and review his examples for how to use it, I feel that you will be close to your goal. If not, post back.