Solved

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

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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