find next function in vba

[Shalender]

hi all.
i am new to vba and facing some problem with find function in vba. actually i want to find a particular value e.g "apple" in a sheet, its working fine with find funcion but the problem is that its showing the first occurence of "apple" and not showing the addres of next occurence of apple.
please help me in solving the problem
thanks & regards

 

[sykes]

Hi Shalender & welcome to the board - stick with it and you'll have some fun, and learn a great deal - I certainly have!

One suggestion for you : always try to explain your question as accurately and fully as possible so that the guys and girls can understand what's needed quickly, and get straight to the point.

I'm making the following assumptions:
1. You know where to put your vb code
2. You know how to run it
If not, ask this first and the guys will go from there.

I've started a blank worksheet "sheet1" and put some fruits into A1 to A10. I've put the word "apple" into A1 and A4.
The following code should give you a message box when run.
Modify the code to suit your needs:

Sub find_apple()
Dim result As String
For Each cell In Sheets("sheet1").Range("a1:a10")
If cell.Value = "apple" Then
result = result + cell.Address(rowabsolute = True, columnabsolute = True) & Chr(13) & vbTab
End If
Next
MsgBox "The following cells contained the word apple:" & Chr(13) & vbTab & result
End Sub

AND LET US KNOW HOW YOU GET ON!

Good luck

Sykes

 

[Richie(UK)]

Hi Shalender,

Welcome to the board.

Don't forget to use the VBE Help files when you are stuck - there is a lot of useful information in them. For example, you will see details on how to find the next item when using Find. A slightly modified version of the example in the Help files is:

Sub Test()
    Dim rngFind As Range, firstAddress As String
    Const strFindMe As String = "apple"
    
    With Worksheets("Sheet1").Cells
        Set rngFind = .Find(what:=strFindMe, LookIn:=xlValues)
        If Not rngFind Is Nothing Then
            firstAddress = rngFind.Address
            Do
                rngFind.Interior.ColorIndex = 3
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress
        End If
    End With

End Sub

HTH

 

[NikolaJovanovic]

Even though it's a 12 year old post a believe that it's the appropriate one for my VBA struggle.

This is the case:

I have two sheets.

Sheet I is a table I want to organize.
Sheet II in column A contains unique values from Sheet I column A. I am using these unique values as a criteria to find each in Sheet I and add certain text into the cell right next to it in the column B.

So, as an inexperienced VBA user, I believe I need some loop, or "for each...", or "find next and then..."
This is the code I'm using at the moment. It stops after copying it once.

Sub FindAccountAndCopyName()

Dim KNAccount As String
Dim NumOfKns As Byte
Dim Found As Range
Dim result As String

With Worksheets("SheetII")
    'NumOfKns = .Cells(.Rows.Count, "A").End(xlUp).Row
    KNAccount = .Range("A1").Value
End With

Application.worksheets(SheetI).Activate
Set Found = Columns(1).Find(what:=KNAccount, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then MsgBox "Account does not exist."
Found.Activate
ActiveCell.Offset(rowOffset:=0, columnOffset:=7).Copy Destination:=ActiveCell.Offset(rowOffset:=0, columnOffset:=1)

End Sub

Please help.

 

[Eric W]

One of the risks of posting an additional question on a 12-year-old post is simply that people won't see it. Your post will briefly show up on the Latest Posts page, but if no one sees it there, you're out of luck.

In any event, here's a basic version of your macro that will loop and find values in your SheetI, column A, and if found, replace column B with column H. This only works for the value found in SheetII, A1. If you want to go down the full list of unique values in SheetII column A, we'll need to add another loop.

Sub FindAccountAndCopyName()

Dim KNAccount As String
Dim r As Long

    KNAccount = Sheets("SheetII").Range("A1").Value
    
    For r = 1 To Sheets("SheetI").Columns("A:A").End(xlDown).Row
        If Sheets("SheetI").Cells(r, "A") = KNAccount Then
            Sheets("SheetI").Cells(r, "B") = Sheets("SheetI").Cells(r, "H")
        End If
    Next r

End Sub

There are some fancier ways to do this, but this is easy to see and maintain.