Help with VBA issue using command button on separate page

aboykov

New Member
Joined
Aug 13, 2019
Messages
5
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi,
welcome to forum

See if this update to your code helps

Code:
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
 
Upvote 0
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.
 
Upvote 0
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

Rich (BB code):
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
 
Upvote 0
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

Rich (BB code):
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!
 
Upvote 0
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?
 
Upvote 0
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

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

for these

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

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

for these

Code:
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


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

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

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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