?
Solved

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

Posted on 2013-10-22
3
Medium Priority
?
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 600 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 1400 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the 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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

764 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