Click here to Skip to main content
14,872,130 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need a help to get vba code with variable that can search value from "text" sheet to "Check" Sheet. I have prepared the code but can not create loop for "Text" sheet form cell value A2 to A4 one by one. Please help for that.

I have created below code

Which shows 1004 error. and i am also not able to crate the loop for all values. please help.

What I have tried:

<pre lang="vb">
Option Explicit

Dim i As Integer
Dim WS, W As Range

Sub Search_fn()

Set WS = Range("B2")

WS = Application.WorksheetFunction.Search(Sheet2.Range("A2"), Range("A2"), 1)

End Sub
Updated 17-Jan-20 2:47am
OriginalGriff 17-Jan-20 2:01am
It's strongly suggest you change your username: Never post your email address in any forum, unless you really like spam! If anyone replies to you, you will receive an email to let you know.

1. The 1004 error with the WorksheetFunction.Search method simply means it was unable to find the text. You could wrap the call in some error handling e.g.
Dim loc As Integer
On Error Resume Next
loc = Application.WorksheetFunction.Search(Sheet2.Range("A2"), Range("A2"), 1)
If Err.Number > 1004 Then
    'text not found
    'loc will contain the point where the text starts
End If
2. You appear to be attempting to find things in a worksheet - that would mean you are using the wrong function - why do I believe this? You are attempting to assign the results of the search to a range - compare WorksheetFunction.Search method (Excel) | Microsoft Docs[^] which returns an integer, versus Range.Find method (Excel) | Microsoft Docs[^], which returns a range.

3. As for the loop - there are many ways - see Solution 1 and please do a little research for yourself before asking questions like that. I would probably go for
Dim sSearchFor As String, rng As Range

For Each rng In ThisWorkbook.Sheets("Text").Range("A2:A4")
    sSearchFor = rng.Value
    ' insert whatever you want to do with the value here
Wendelius 17-Jan-20 14:50pm
Nice example

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900