Solved

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

Posted on 2013-10-22
3
424 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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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