Find Next Instance

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
693
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

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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Anyone?

Please let me know if I'm not explaining myself clearly enough, and I will try to explain in further detail.
 
Upvote 0
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.
 
Upvote 0
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.

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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top