Find Next Instance

[Noz2k]

Ok I have some code which looks up a value in row 1 of sheet1 in a sheet specified by column A.

It then returns the value in the column before where the value is found.

However I am having a problem that there can be more than 1 instance of the value on the sheet (sheetP).

In this instance I would like to return the sum of the values, any help would be greatly appreciated.

Here is my code

Sub GetValues()

Dim P As String, _
ws1 As Worksheet, _
ws2 As Worksheet, _
Y As String, _
X As String, _
rng As Range
Set ws1 = Sheets("Sheet1")
lnglastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Y = 2
Do
P = ws1.Range("A" & Y)
Set ws2 = Sheets(P)
X = 2
Do
WeekStart = ws1.Range("A1").Offset(0, X)
With ws2.Range("D:D")
Set rng = .Find(WeekStart, , xlValues)
End With
If Not rng Is Nothing Then
        rng1 = rng.Address
 
ws1.Range("A" & Y).Offset(0, X) = rng.Offset(0, -1)
End If
X = X + 2
Loop Until X > 6

Y = Y + 1
Loop Until Y = lnglastRow + 1
End Sub

 

[Noz2k]

Anyone?

Please let me know if I'm not explaining myself clearly enough, and I will try to explain in further detail.

 

[Rick Rothstein]

Anyone?

Please let me know if I'm not explaining myself clearly enough, and I will try to explain in further detail.

For me personally, I would like to see more description. Instead of posting code and making us figure out what it does (especially without being able to see the data it is supposed to be processing), it would be more useful if you describe your data (even better would be to post some samples of it) and tell us what you hoped the code you posted would being doing to it.

 

[Noz2k]

Ok,

So managed to solve the problem, so for anyone interested in future.

I added another loop at the innermost level, and used .FindNext to determine whether there was another instance. I then added this next value to what had currently been returned to the appropriate cell.

I also had to add some code at the beginning in order to clear the data else it would add the total cumatively on top of the last each time the procedure was ran.

Here is the code.

Sub GetValues()
 
' Sets Variables
 
Dim P As String, _
ws1 As Worksheet, _
ws2 As Worksheet, _
Y As String, _
X As String, _
rng As Range, _
lngLastColumn As Long, _
lngLastRow As Long, _
LastEntry As Long
 
'Sets Sheet1
 
Set ws1 = Sheets("Sheet1")
 
' Determines the last row and the last column in the table
 
lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lngLastColumn = Sheets("Sheet1").Cells("1", Columns.Count).End(xlToLeft).Column
 
' Sets the bottom right corner of the table to the variable 'Last Entry'
 
LastEntry = ws1.Cells(lngLastRow, lngLastColumn).Address
 
' Clears all values in the table
 
ws1.Range("C2", LastEntry).ClearContents
 
' Sets the initial value for Y, this corresponds to the first row in the table
 
Y = 2
 
' Initialises the outermost loop
 
Do
 
' Sets P as the name instance in column A
 
P = ws1.Range("A" & Y)
 
' Sets ws2 as the Sheet with name 'P'
 
Set ws2 = Sheets(P)
 
' Sets the initial value for X, this corresponds to the column number
 
X = 2
 
' Initialises Loop
 
Do
 
' Looks along row 1, and assigns WeekStart to the Value at column X
 
WeekStart = ws1.Range("A1").Offset(0, X)
 
' Sets where to search for the value
 
With ws2.Range("D:D")
 
' When the value is foung it sets this as the range
 
Set rng = .Find(WeekStart, , xlValues)
 
' Determines whether a value was found
 
If Not rng Is Nothing Then
        rng1 = rng.Address
Do
 
' Adds the value into the appropriate column in Sheet1, if there is another 
instance, it adds this on top.
 
ws1.Range("A" & Y).Offset(0, X) = rng.Offset(0, -1) + ws1.Range("A" & Y).Offset(0, X)
Set rng = .FindNext(rng)
 
Loop While Not rng Is Nothing And rng.Address <> rng1
End If
End With
 
' Increases the value of X by 2, to perform the same for the new column in that row
 
X = X + 2
 
Loop Until X > lngLastColumn - 1
 
' Increases the value of Y by 1, to perform the same for the new row in the table
 
Y = Y + 1
 
Loop Until Y > lngLastRow
 
End Sub