Solved

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

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

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 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

20 Experts available now in Live!

Get 1:1 Help Now