Solved

Using "Find" with VBA to search through a column of Values.

Posted on 2013-10-22
3
414 Views
Last Modified: 2013-10-23
Dear Experts.  I have, I hope, a relatively simple problem.
I like to keep code simple and use the minimum number of lines possible, while keeping it robust.

I have attached a simple table.
I wish to find each occurence of "F" in Column 2
Having found "F" put a value in Column 7

I can do this using loop, but it seems to me that "Find" is a faster way of doing this, without looping through each line in the range.

Please can you have a look at the code in the attached file and help me find the next line.
Many thanks
David
Option Explicit
Sub FindAndOffset()

'This routine should find a specific value within a column and copy a corresponding value from another column in the same row.
'E.g. If a fare is "Fixed", rather than calculated, copy the fare from the "Client Fare" Column to the "Amended Fare" Column.

Dim rFixed As Range ' Set Range to find fixed fares in Column B (Column 2)

Set rFixed = Range(Cells(2, 2), Cells(1, 2).End(xlDown))
Debug.Print rFixed.Address

'If FixedFare = "F", then copy the Client Fare (Column D - Column 4) to (Column G  - Column 7)

    rFixed.Find(What:="F").Offset(0, 5) = rFixed.Find(What:="F").Offset(0, 2)
    'Next Find?????

'I am trying to do this without using a loop. Is this possible

End Sub

Open in new window

FindAndOffset.xlsm
0
Comment
Question by:David Phelops
3 Comments
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 150 total points
ID: 39591686
Hi,

pls try

 
Set c = rFixed.Find("F", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Offset(0, 5) = c.Offset(0, 2)
            Set c = rFixed.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If

Open in new window

Regards
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 350 total points
ID: 39593071
In theory you could avoid using the loop if you knew how many Fs will be found and program the same lines of code that many times. In practise, your requirement is exactly what loops are good at and a looped solution is much easier to read and use than a lengthy, repetitive one. Please try this code,
Option Explicit

    ' Enums without specific value assignment
    ' take the value of the preceding enum in the list + 1
    
    Private Enum Nws                    ' Worksheet navigation
        NwsFixed = 2                    ' column B = 2
        NwsMiles
        NwsClient
        NwsAmended = 7
    End Enum
    
Sub FindAndOffset()

    ' This routine should find a specific value within a column and
    ' copy a corresponding value from another column in the same row.
    ' E.g. If a fare is "Fixed", rather than calculated, copy the fare
    ' from the "Client Fare" Column to the "Amended Fare" Column.
    
    Dim SearchRange As Range
    Dim Fnd As Range
    Dim R As Long
    
    With ThisWorkbook.ActiveSheet
        Set SearchRange = .Columns(NwsFixed)
        Set Fnd = SearchRange.Find("F")
        Do While Not Fnd Is Nothing
            If Fnd.Row < R Then Exit Do
            R = Fnd.Row
            .Cells(R, NwsAmended).Value = .Cells(R, NwsClient).Value
            Set Fnd = SearchRange.FindNext(Fnd)
        Loop
    End With
End Sub

Open in new window

I am not a fan of the Offset function in VBA because such instructions are impossible to read in VB without looking at the worksheet, and even then they require mental acrobatics. I think this line of code is much easier to understand,
.Cells(R, NwsAmended).Value = .Cells(R, NwsClient).Value
This kind of addressing is enabled by the Enum at the top of the code. In future, if you happen to add or remove columns in your worksheet, you can just change the values assigned to the enumerations in the enum and the code will work with your new worksheet. It is the simplest and fastest way for implementing modifications.
0
 

Author Closing Comment

by:David Phelops
ID: 39593730
Two solutions are very helpful and work.

The solution from Faustulus takes the question a step further and gives specific relevant information as how to improve code robustness  -  that will be far-reaching for future programming for me.

Many thanks both
David
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now