Error # 448 when using 'Find Next'

[noboffinme]

Hi

I have 2 w/shts, 'A' & 'B', W/sht 'A' has a column 'A' of Numbers where each number appears at least twice.

In column 'C' on the same w/sht 'A' there are values I want to hold as variables to be pasted onto W/sht 'B' against that same number.

So I have some code that selects a number from W/sht 'B' & goes over to W/sht 'A' to search column 'A' for the first occurrence of that number.

Once the code finds the number, it saves the value in the same Row on Column 'C' as a variable.

My problem is getting the code to then go & find the next occurence of the same number, go to the Column 'C' value & save that as a variable etc etc until I have all the variables for that number.

I use such variables as 'planx' & 'plan1' etc to hold the variable value.

I recorded a macro to do a 'Find' & 'FindNext' function & am trying to adapt it for what I need.

The first Find is OK & the address of the Column 'C' value id held, however when I go to the FindNext line of code - I firstly get an 'Object Variable not set' error. When I try to fix this by adding the 'what:= .... ' part of the criteria, I get the 448 error.

Can anyone advise what I could do to fix this OR suggest a better way to do what I want to do?

Below is the code throwing the error, Thanks;

#################################################
Recorded code. works OK;

Selection.Find(what:=acno, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
----------------------------------------------------------------
My additonal code is next to hold the variable address, works OK;

ActiveCell.Offset(0, f).Select
planx = ActiveCell.Address
plan1 = planx
planx = ""

As soon as I get to this line below (after I added the 'what:=acno,' part, I get the # 448 error.
-------------------------------------------------------------
Selection.FindNext(what:=acno, After:=ActiveCell).Activate
-------------------------------------------------------------

My code again next to hold the variable
ActiveCell.Offset(0, f).Select
planx = ActiveCell.Offset(0, f).Value
plan2 = planx
planx = ""

 

[AlphaFrog]

The only argument for .FindNext is the After argument. The .FindNext method doesn't use a What argument. The What is the same as the previous .Find

Selection.FindNext(After:=ActiveCell).Activate

 

[noboffinme]

Thanks, so that an answer to why it won't work, do you have any suggestions that will work?

 

[AlphaFrog]

Can you post your entire macro code as it is? All of it.

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

 

[noboffinme]

OK, Here it is;

Option Explicit
Sub extract_plans()
Dim a, b, c, f As Integer
Dim i, j, k, l As Long
Dim acno As String
Dim rawacno As String
Dim planx As String
Dim plan1 As String
Dim plan2 As String
Dim plan3 As String
Dim plan4 As String
Dim plan5 As String
Dim plan6 As String
Dim plan7 As String
Dim plancount As Integer
'Select the worksheet with the A/c nos & Plan Names
Sheets("PIVOT_SUMMARY").Select
'Loop through each A/c no up to row 2
For a = Range("A65536").End(xlUp).Row To 2 Step -1
 
'The variable 'a' needs to read the value one row up as the bottom value is 'Grand Total'
a = a - 1
'Gather the value for the Current A/c number to locate it in the 'RAW_DAT' wksht
        
'hold the acno temp value
acno = Cells(a, "A").Select
acno = Cells(a, "A").Value
                
                                '----------------------------------------------------------------------------------------------
                                'Inner Loop from each a/c no to locate each plan name from the 'RAW_DATA' wkhst
                                'to paste into the 'PIVOT_SUMMARY' wksht
                                               
                                'The variable 'plancount' is the number of plans that the A/c has had
                                'retreived from the 'PIVOT_SUMMARY' wksht column 'HG'
                                plancount = Sheets("PIVOT_SUMMARY").Range("A1:A40000").Find(what:=acno).Offset(0, 214).Value
                               
                                Sheets("RAW_DATA").Select
                                
                                'Variable 'f' holds the value number of the Column for the Plan Names
                                f = 10
                                
                                For b = Range("B65536").End(xlUp).Row To plancount Step -1
                                
                                rawacno = Cells(b, "B").Select
                                rawacno = Cells(b, "B").Value
                                
                                                                                                
                                Columns("B:B").Select
                               
                                    Columns("B:B").Select
                                    Selection.Find(what:=acno, After:=ActiveCell, LookIn:= _
                                        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                                        xlNext, MatchCase:=False, SearchFormat:=False).Activate
                                        ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Address
                                        plan1 = planx
                                        planx = ""
                                        
                                    Selection.FindNext(what:=acno, After:=ActiveCell).Activate
                                    ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Offset(0, f).Value
                                        plan2 = planx
                                        planx = ""
                                        
                                    Selection.FindNext(After:=ActiveCell).Activate
                                    ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Offset(0, f).Value
                                        plan3 = planx
                                        planx = ""
                                        
                                    Selection.FindNext(After:=ActiveCell).Activate
                                    ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Offset(0, f).Value
                                        plan4 = planx
                                        planx = ""
                                        
                                    Selection.FindNext(After:=ActiveCell).Activate
                                    ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Offset(0, f).Value
                                        plan5 = planx
                                        planx = ""
                                        
                                    Selection.FindNext(After:=ActiveCell).Activate
                                    ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Offset(0, f).Value
                                        plan6 = planx
                                        planx = ""
                                        
                                    Selection.FindNext(After:=ActiveCell).Activate
                                    ActiveCell.Offset(0, f).Select
                                        planx = ActiveCell.Offset(0, f).Value
                                        plan7 = planx
                                        planx = ""
                                                              
                                Next b
                                                       
                                Sheets("PIVOT_SUMMARY").Select
                                                     
Next a
             
             
             
End Sub

 

[AlphaFrog]

I have no idea what you are ultimately trying to do. The code below tries to mimic your original code and is only offered as an example.

Sub extract_plans()

    Dim wsPS As Worksheet, wsRD As Worksheet
    Dim Found As Range
    Dim Plancount As Long, i As Long, NamesCol As Long
    Dim PlanNames() As String
    
    Set wsPS = Worksheets("PIVOT_SUMMARY")
    Set wsRD = Worksheets("RAW_DATA")
    NamesCol = 10 ' Column offset for the Plan Names
    
    For Each acno In wsPS.Range("A2", wsPS.Range("A" & Rows.Count).End(xlUp).Offset(-1))
    
        Plancount = acno.Offset(0, 214).Value   'Count of ac nos from PIVOT_SUMMARY
        
        Set Found = wsRD.Range("B:B").Find(What:=acno.Value, _
                                           LookIn:=xlFormulas, _
                                           LookAt:=xlPart, _
                                           SearchOrder:=xlByRows, _
                                           SearchDirection:=xlNext, _
                                           MatchCase:=False)
        If Not Found Is Nothing Then
            ' Match found
            i = 1
            ReDim PlanNames(1 To Plancount)
            Do
                PlanNames(i) = Found.Offset(, NamesCol).Value
                Set Found = wsRD.Range("B:B").FindNext(After:=Found)
                i = i + 1 'Loop counter
            Loop Until i > Plancount
            
            ' Display Plan Names just for fun
            strMsg$ = ""
            For i = 1 To UBound(PlanNames)
                strMsg = strMsg & PlanNames(i) & vbLf
                If i > 20 Then Exit For ' Stop after 20 names max
            Next i
            MsgBox "acno =" & acno & "  Count=" & Plancount & vbLf & strMsg
            
        Else
            ' No acno match found
            MsgBox "No match found for " & acno, vbExclamation, "No Match Found"
        End If
        
    Next acno

End Sub

 

[noboffinme]

Hi AlphaFrog

I've explained what I'm trying to do & this is the code that is working up to finding the next number value.

It is complete up to the FindNext line which is where the problem begins.

If you can't understand it yet, then perhaps we should let someone else answer.

Your responses have not been helpful at all...

 

[AlphaFrog]

You're right. I'll not bother you again.

 

[noboffinme]

Hi

In case anyone needs to do this as described above, I have the answer, in order to save the 'found' value for pasting later & avoid the error codes, the code is as follows;

Firstly - To activate the 'Found' cell;

NOTE - 'acno' is the variable name

I found that the below is fine to use to locate the cell, same as the recorded code.

Selection.Find(What:=acno, after:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Next - To hold the value in order to paste onto the second spreadsheet using the variable 'plan1', 'f' is the number of the column that holds the plan value;

        plan1 = ActiveCell.Cells.Offset(0, f).Value

I used this for as many variables as I needed & it worked fine.