Help with VBA issue using command button on separate page

[aboykov]

Hi All,

having an issue with my code and hoping someone can help.

I have a workbook that has 2 sheets, 1 is the master data sheet the other is the active sheet "Allowed repair" with 2 buttons, search and clear contents. I have set the command button to execute the following VBA macro in a module but keep getting an error for a not set object.

Originally my code was written on the data sheet and it searches that sheet and exports the output to sheet #2 .

The macro still runs correctly when running from master data sheet but not when the macro is called from the command button on the active sheet.

here is my macro

Sub SearchForString()


Dim LSearchValue As String
Dim RefCount As Integer


LSearchValue = InputBox("Please enter a value to search for.", "Enter value") & "*"
RefCount = Application.WorksheetFunction.CountIf(Sheet1.Range("A:L"), LSearchValue) - 1
Range("A1").Activate
For Counter = 0 To RefCount Step 1
With Columns("A:L").Find(What:=LSearchValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
.EntireRow.Copy Worksheets("Allowed repair").Range("A65536").End(xlUp).Offset(1, 0)
.Activate
End With
Next
End Sub

Thank you

 

[dmt32]

Hi,
welcome to forum

See if this update to your code helps

Sub SearchForString()
    Dim LSearchValue As String
    Dim RefCount As Long
    Dim FoundCell As Range
    Dim wsMasterData As Worksheet, wsAllowedRepair As Worksheet
    
    Set wsMasterData = Sheet1
    Set wsAllowedRepair = Worksheets("Allowed repair")
    
    Do
        LSearchValue = InputBox("Please enter a value to search for.", "Enter value") & "*"
'cancel pressed
        If StrPtr(LSearchValue) = 0 Then Exit Sub
    Loop Until Len(LSearchValue) > 0
    
    
    With wsMasterData
        Set FoundCell = .Range("A1")
        For RefCount = 1 To Application.CountIf(.Range("A:L"), LSearchValue)
            Set FoundCell = .Columns("A:L").Find(What:=LSearchValue, After:=FoundCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            FoundCell.EntireRow.Copy wsAllowedRepair.Cells(wsAllowedRepair.Rows.Count, 1).End(xlUp).Offset(1, 0)
       Next
    End With
End Sub

Dave

 

[aboykov]

Thanks Dave!

That helped a lot!

If you have a minute can you try to explain what i was doing wrong? im trying to learn this as i go.

 

[dmt32]

Thanks Dave!

That helped a lot!

If you have a minute can you try to explain what i was doing wrong? im trying to learn this as i go.

The Columns reference (shown in bold) does not have a workbook and worksheet associated with it. Without an explicit reference, Columns refers to the ActiveSheet by default. So your code may fail (produce incorrect results) when as you discovered, you try to use it whilst another sheet is active.
Also, the After parameter (shown in RED) can generate an error using ActiveCell if the sheet being searched is not Active

With Columns("A:L").Find(What:=LSearchValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
.EntireRow.Copy Worksheets("Allowed repair").Range("A65536").End(xlUp).Offset(1, 0)
.Activate
End With

Dave

 

[aboykov]

The Columns reference (shown in bold) does not have a workbook and worksheet associated with it. Without an explicit reference, Columns refers to the ActiveSheet by default. So your code may fail (produce incorrect results) when as you discovered, you try to use it whilst another sheet is active.
Also, the After parameter (shown in RED) can generate an error using ActiveCell if the sheet being searched is not Active

With Columns("A:L").Find(What:=LSearchValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
.EntireRow.Copy Worksheets("Allowed repair").Range("A65536").End(xlUp).Offset(1, 0)
.Activate
End With

Dave

Ahh ok i see thank you so much!

 

[aboykov]

one more question if i want to alter the Cell it copies to instead of A1 to say G22 how would i go about doing that?

 

[dmt32]

one more question if i want to alter the Cell it copies to instead of A1 to say G22 how would i go about doing that?

You currently copy the EntireRow so to place copied data to Range that is not the same size, you will need to size the data you are copying

something like this

change this line

FoundCell.EntireRow.Copy wsAllowedRepair.Cells(wsAllowedRepair.Rows.Count, 1).End(xlUp).Offset(1, 0)

for these

LastCol = .Cells(FoundCell.Row, .Columns.Count).End(xlToLeft).Column
            .Cells(FoundCell.Row, 1).Resize(, LastCol).Copy wsAllowedRepair.Cells(wsAllowedRepair.Rows.Count, 7).End(xlUp).Offset(1, 0)

This assumes that you have data in G22 onward

Dave

 

[aboykov]

You currently copy the EntireRow so to place copied data to Range that is not the same size, you will need to size the data you are copying

something like this

change this line

FoundCell.EntireRow.Copy wsAllowedRepair.Cells(wsAllowedRepair.Rows.Count, 1).End(xlUp).Offset(1, 0)

for these

LastCol = .Cells(FoundCell.Row, .Columns.Count).End(xlToLeft).Column
            .Cells(FoundCell.Row, 1).Resize(, LastCol).Copy wsAllowedRepair.Cells(wsAllowedRepair.Rows.Count, 7).End(xlUp).Offset(1, 0)

This assumes that you have data in G22 onward

Dave

You are a life saver.. learning a lot here,

So if i wanted for the search to output a message that says value not found or whatever i would just add an if statement after the search value?

Do
LSearchValue = InputBox("Please enter a value to search for.", "Enter value") & "*"
'cancel pressed
If StrPtr(LSearchValue) = 0 Then MsgBox "Not found...", vbExclamation
Then

Exit Sub
Loop Until Len(LSearchValue) > 0


??

 

[dmt32]

So if i wanted for the search to output a message that says value not found or whatever i would just add an if statement after the search value?

Normally, you test in the Range.Find function but you have a search box so can test for no match there & give user opportunity to enter another value

Sub SearchForString()
    Dim LSearchValue As String, Default As String
    Dim RefCount As Long, MatchCount As Long
    Dim FoundCell As Range
    Dim wsMasterData As Worksheet, wsAllowedRepair As Worksheet
    
    Set wsMasterData = Sheet1
    Set wsAllowedRepair = Worksheets("Allowed repair")
    
    Do
        LSearchValue = InputBox("Please enter a value to search for.", "Enter value", Default) & "*"
'cancel pressed
        If StrPtr(LSearchValue) = 0 Then Exit Sub
        If Len(LSearchValue) > 0 Then
        MatchCount = Application.CountIf(wsMasterData.Range("A:L"), LSearchValue)
        If MatchCount = 0 Then MsgBox LSearchValue & Chr(10) & "Record Not Found", 48, "Not Found"
        Default = LSearchValue
        End If
    Loop Until MatchCount > 0
    
    
    With wsMasterData
        Set FoundCell = .Range("A1")
        For RefCount = 1 To MatchCount
            Set FoundCell = .Columns("A:L").Find(What:=LSearchValue, After:=FoundCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            LastCol = .Cells(FoundCell.Row, .Columns.Count).End(xlToLeft).Column
            .Cells(FoundCell.Row, 1).Resize(, LastCol).Copy wsAllowedRepair.Cells(wsAllowedRepair.Rows.Count, 7).End(xlUp).Offset(1, 0)
       Next
    End With
End Sub

Dave