How to use column of strings to find that same string in another sheet, and paste a corresponding value from sheet 1 next to the found value?

Question

I am working on generating some automated reports. To reiterate, I would like my code to look at each "string" in Sheet1 column E (ignoring blanks), search for that value in another sheet/workbook, and paste the "single" contained in Sheet1 column D (ignoring blanks, if E is blank, D will be blank) to the left of the string found in the search earlier.

The receiving data sheets are widely dispersed and in a variety of formats, but the one constant is that each position for data input has a unique tag in an adjacent column that is also found in the primary data storage sheet. I don't have that much experience in VBA yet, and was wondering how some others would solve this problem in a dynamic way that wont break the minute a new column is added.

My current code is a jumbled mess, so any pointers, ideas, general strategies would be appreciated. I am trying to get away from using Sheets().select and other references like this, but I'm not sure how to do that yet.

Dim location As String
Dim rownum As Integer
Dim cellfinder As Integer

Sheets("Sheet2").Select                          'Ensures that we start on sheet 2

rownum = Range("G2").Value


For cellfinder = 1 To rownum                     'Loop goes for as many non-blank rows exist in column---- need
    'to add code to skip over blank rows with offset function or else loop will not go on long enough.

    Sheets("Sheet2").Select                      'selects Pi tag data sheet

    'hopefully adjusts the active cell relative to the loop count

    If ActiveCell.Value = "" Then                'hopefully detects blank cells and skips to next loop
        GoTo Skip
    End If

    location = ActiveCell.Value                  'calls the location tag string the variable "location"
    ActiveCell.Offset(0, -1).Select              'offsets from location tag to the "current value column"
    ActiveCell.Value.Copy                        'copies the value found in the current value column hopefully not the pi function


    Sheets("Sheet1").Select                      'Selects EOM sheet, can be whatever sheet we wish, or in another worksheet
    Range("A1").Select                           'establishes a starting point for find function

    Cells.Find(What:="location", After:=sht2.cells(1,1), LookIn:= _
               xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
               xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -1).Select              'offsets the active cell to the value column in EOM sheet
    ActiveCell.Paste                             'pastes the activecell value copied from sheet 1
    ' find function finds the string saved to the location variable , makes it the active cell.

    Skip:
Next cellfinder

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also make sure all your row counting variables are declared `As Long` Excel has more rows than `Integer` can handle. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Feb 04 '19 at 14:47
@Nathan_Sav good suggestion I forgot that was a thing. I will update shortly after I've evaluated it. Edit: I don't believe VLOOKUP will suffice, it seems to mandate that the searched value is presented in a table, but the data sheets I am working with are formatted differently in every department, I'd need to submit paperwork to get them changed and would have to train some of our operators who input data manually. Due to the nature of presented information, making the report sheets into tables would be ineffective. — Steve Vitale, Feb 04 '19 at 14:53

Tate Garringer · Accepted Answer · 2019-02-04T15:43:26.457

One thing to note is that in What:="location" when you put location in quotations, you're telling your find function to look exclusively for the value "location" instead of the variable location which you've set to the value of the ActiveCell in the loop.

Additionally, I'd avoid using Goto unless you're exiting a deeply nested loop. You could rewrite your code to avoid using Select, ActiveCell, Goto and correcting your .Find like:

Dim location As String
Dim rownum As Long
Dim cellfinder As Long
Dim fRng As Range

With Sheets("Sheet1")

    rownum = .Range("E" & Rows.Count).End(xlUp).Row

    For cellfinder = 1 To rownum

        If .Range("E" & cellfinder) <> "" Then

            location = .Range("E" & cellfinder)

            Set fRng = Sheets("Sheet2").Cells.Find(What:=location, LookIn:=xlFormulas, LookAt:=xlPart) '<-Sheets("Sheet2") can be any sheet you need to perform the .Find against

            If Not fRng Is Nothing Then
                fRng.Offset(0, -1) = .Range("D" & cellfinder)
            Else
                'Do something when .Find doesn't find anything
            End If

        End If

    Next cellfinder

End With

Using a With block allows you to specify the desired object once and utilize it by prefixing methods with a . so that

With Sheets("Sheet1")
    .Range("A1")
End With

Is functionally the same as

Sheets("Sheet1").Range("A1")

Using .Range("E" & Rows.Count).End(xlUp).Row will return the number of the last used row in Column E, then you can test for blank cells in the loop and not worry about your rownum count not being long enough when you let it = Range("G2").

To circumvent the Goto statement, using the opposite operator and enclosing the entire subsequent executing code within an If statement would achieve the same result without encouraging spaghetti code. Instead of testing to see if ActiveCell = "" and then executing a Goto when it does, test to see if it doesn't, only executing the following code when it doesn't.

Initializing fRng as a Range and setting it equal to the result of your .Find function will allow you to test the find function to see if it returned anything so you don't error out when you try to do something with fRng when it's Nothing.

Thank you for working through my mess of code and providing such a thorough response. I really appreciate it. I will incorporate your suggestions and update later. — Steve Vitale, Feb 04 '19 at 15:29
How will the find function respond to searching for a value that doesn't exist in the searched sheet? does it yield a "nothing" value? If not, how do I prepare for an incorrect input in the first string column? — Steve Vitale, Feb 04 '19 at 15:39
That is correct, it will return `Nothing` if nothing is found. The subsequent `If Not fRng is Nothing Then` will test for nothing being returned. If you wanted to do something when `fRng Is Nothing` you could add an `Else` to the `If` statement where `fRng` is tested. I'll update my answer to reflect. — Tate Garringer, Feb 04 '19 at 15:41

How to use column of strings to find that same string in another sheet, and paste a corresponding value from sheet 1 next to the found value?

1 Answers1