Find Value on the other worksheet - 6 lines of code - something is wrong

[makiwara]

Hi! Thank you for reading my quick question!

What I need: I select a cell, then a variable stores its value, and looks for it on worksheet "1" in column ("B:B").

Sub findstring()
Dim CurrValue As String


CurrValue = ActiveCell.Value



Sheets(1).Columns("B:B").Find(What:=CurrValue, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub

I highlighted the problem. (synctax error according to the debugger) Thank you for your answer!

 

[dmt32]

Hi,
Your code written that way can produce either a type mismatch error if search value not found or Activate Range Of Class error if the search sheet is not the active sheet.
Also, your published code is missing a line continuation character _ which will cause compile error.

You can resolve these issues by modifying the code as follows:

 Sub findstring()    
    Dim CurrValue As String
    Dim Foundcell As Range
    
    
    CurrValue = ActiveCell.Value
    
    If Len(CurrValue) = 0 Then Exit Sub
    
    Set Foundcell = Sheets(1).Columns("B:B").Find(What:=CurrValue, After:=ActiveCell, LookIn:=xlFormulas, _
                                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                MatchCase:=False, SearchFormat:=False)
                                                
    If Not Foundcell Is Nothing Then
'value found do stuff here
        MsgBox Foundcell.Address
    Else
'not found
        MsgBox CurrValue & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If


End Sub

Dave

 

[makiwara]

Thank you Dave for the quick response!

I created a button, and I inserted your code to that, so if I was select a cell and click the button, it has to jump to the Sheet("1") and look for the cell's content in column "B".

So I expected that everything will be all right, but unfortunately the code didn't run. Have I done something wrong?
https://imgur.com/a/thqtnYe

Thank you for your answer , it means to me a lot! Have a nice day!

But

Hi,
Your code written that way can produce either a type mismatch error if search value not found or Activate Range Of Class error if the search sheet is not the active sheet.
Also, your published code is missing a line continuation character _ which will cause compile error.

You can resolve these issues by modifying the code as follows:

 Sub findstring()    
    Dim CurrValue As String
    Dim Foundcell As Range
    
    
    CurrValue = ActiveCell.Value
    
    If Len(CurrValue) = 0 Then Exit Sub
    
    Set Foundcell = Sheets(1).Columns("B:B").Find(What:=CurrValue, After:=ActiveCell, LookIn:=xlFormulas, _
                                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                MatchCase:=False, SearchFormat:=False)
                                                
    If Not Foundcell Is Nothing Then
'value found do stuff here
        MsgBox Foundcell.Address
    Else
'not found
        MsgBox CurrValue & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If


End Sub

Dave

 

[dmt32]

code ran ok for me.
what error is being reported?

Dave

 

[makiwara]

https://imgur.com/a/kVkjjSo
If I select a cell in a column OTHER THAN "B" the code doesnt work.
However the Column "B" means the "B" column of Worksheet "1" so for the first sight in doesnt make sense.

Any idea? Really thank you for your time!

code ran ok for me.
what error is being reported?

Dave

 

[makiwara]

Ok its solved!
I deleted this : After:=ActiveCell," and after that it worked thank you again!!! )