• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

search function in vba in specific range and specefic search criteria

A

Amit Modi

Member
Hi guys,
Thanks for last help.
Now I have two question for same code.
Question1:
I have a following code in vb.
Private Sub CommandButton1_Click ()
Sheet1.Range ("j13").Value = Application.WorksheetFunction.Search ("DBDS6", [i13])
End Sub
When I press command button its works perfectly.
Now I want to apply same formula in my range from [j13] to[j50] with reference of [i13] to [i50].
Something like here below.
Sheet1.Range ("j13").Value = Application.WorksheetFunction.Search ("DBDS6", [i13])
Sheet1.Range ("j14").Value = Application.WorksheetFunction.Search ("DBDS6", [i14])
Sheet1.Range ("j15").Value = Application.WorksheetFunction.Search ("DBDS6", [i15])
Sheet1.Range ("j16").Value = Application.WorksheetFunction.Search ("DBDS6", [i16])
Sheet1.Range ("j17").Value = Application.WorksheetFunction.Search ("DBDS6", [i17])
.
.
.
.
.
.
Sheet1.Range ("j50").Value = Application.WorksheetFunction.Search ("DBDS6", [i50])
Now I want this as a short program. Please help me.
Now
Question2:
I want to search multiple name from row(‘s) of column “I” using some code, my above code search only for product “DBDS6”,
I want function to search more name as below.
i.e “KPK6”
“DBDS6”
“MNB6”
“FGR6” etc….
Please tell me is it possible??? If yes then please help me
Thank you.
 
Hi Amit,

Try below code for your Q1:

Code: 
Private Sub CommandButton1_Click()


For j = 13 To 50
On Error Resume Next
Sheets("Sheet1").Cells(j, 10) = Application.WorksheetFunction.Search("DBDS6", Sheets("Sheet1").Cells(j, 9))
Next j


End Sub

Regards,
 
sir please help me for my Que:2 I have 100 items and its not an easy to create so much code,as I am learner/beginner so please if its not possible in vba code than please give formula for simple excel formula of possible thank you
 
One more option:

Code: 
Private Sub CommandButton1_Click()

For j = 13 To 50
    If InStr(1, Range("I" & j), "DBDS6", vbTextCompare) Then
        Range("j" & j) = InStr(1, Range("I" & j), "DBDS6", vbTextCompare)
    End If
Next j

End Sub

Regarding your Q2 can you post a sample file?



Regards,
 
@Amit Modi

can you upload the list of Words to be searched along with their code which needs to be returned?

EDIT: see the file, for formula solution. Note the formula in orange cells is an array formula, which must be confirmed with Ctrl+Shift+Enter.
Regards
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top