Cannot use .Find commands as part of mini loops inside a larger loop

[iam_011]

Hello all,

I am trying to write a loop with two mini internal loops as part of a larger process.

The whole major looped process is located in one sheet (“Set up”).

It is intended to take a value (starts at 1) in cell B1 and search for that value in a fixed range E3:E500. I use a formula in cell C1 to determine if that search value exists in the range or not. If not, it increases the value in B1 by 1 and searches again, increasing the value by 1 each time (1 then 2 then 3 etc.) within the first mini loop until it finds the value in search range. When it finds the first instance of the value it copies the data in same row offset by 1 to 14 columns to right of the found value. So far so good although I had to use an alternative to the .find function to get it to work (not shown here).

The process is then designed to then take the copied data and find the LAST instance of the same value in the same search range and paste the data into the corresponding offset columns to the right. This is where I have hit a dead end, as I can't find any other function than .find to get and select the location of the last instance in the range without using the .find command.

The second mini loop is then intended to find each of the instances in the range of the search value and clear the contents of the offset column until there's only one instance left (the last instance against which the data has just been copied). This is dictated by a formula in cell C1 which does the count of the value and some other criteria which I couldn't write the code for. Once there's only 1 instance of the value left, it is then supposed to exit the second loop and increase the search value in B1 by 1 and start the whole loop again to find the new value. Every time I run it the debug highlights the following rows of code which use the find command.

grprange.Find(grpnumber).Activate

Set myC = grprng.Find(grpnumber, , , , , xlPrevious)

It throws up the following error message "Run-time error '91': Object variable or With block variable not set". I assume this is because the find function is now fragmenting the loop functions.

Below is code, please let me know where / why I am going wrong and if there’s an alternative (I am sure there is) which someone of my limited vba skills could use.

Thanks

Dim countocc As Range
Dim endproc As Range
Dim grprng As Range
Dim grpnumber As String
Dim grpnum As Range

grpnumber = ThisWorkbook.Worksheets("Set Up").Range("$B$1")
Set grpnum = ThisWorkbook.Worksheets("Set Up").Range("$B$1")

Set endproc = ThisWorkbook.Worksheets("Set Up").Range("$A$1")
Set countocc = ThisWorkbook.Worksheets("Set Up").Range("$C$1")

‘main loop start
Do While grpnum <= endproc

‘mini loop 1 start

Do While countocc = 0

grpnum.Value = grpnum + 1

Loop

Set grprng = ThisWorkbook.Worksheets("Set Up").Range("E3:E500")
grpnumber = ThisWorkbook.Worksheets("Set Up").Range("$B$1")

grprange.Find(grpnumber).Activate

Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 14)).Copy

Dim myC As Range
Set myC = grprng.Find(grpnumber, , , , , xlPrevious)

myC.Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

grprng.Find(grpnumber).Select

Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 14)).Select
Selection.ClearContents

‘mini loop 2 start

Dim r As Range
Dim v As Variant

For Each r In Intersect(Range("E2:E500"), ActiveSheet.UsedRange)
v = r.Value
If Range("$C$1") = 1 Then Exit For
If InStr(v, grpnumber) > 0 Then
r.Select
Range(Selection.Offset(0, 1), Selection.Offset(0, 14)).ClearContents
End If

Next r

Range("B1").Select
Selection.Value = Range("B1") + 1

Loop

[mehmetcik]

Welcome to the Forum.

unfortunately your post will upset our moderators do few people will respond.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)