VBA search column for matching string Solved/Closed

Question

I am trying to write a script in VBA for excel.

For an entire column of data in sheet 2, for each individual cell in a column, I need to search a column in sheet 1 for a string that matches the value, and then copy paste certain cells from the same row in sheet 1 back to sheet 2.

I can do the copy pasting, but I cannot figure out how to search a column for matching strings on a separate sheet and do the search for all values in a given column.

rizvisa1 · Accepted Answer

Sub SeekFishInfo()  
Dim lMaxRows As Long  

    Sheets("Colour Spots").Select  
      
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row  
      
    If lMaxRows < 2 Then Exit Sub  
       '=VLOOKUP(B2,'Fish  List'!A:E,2,0)  
    With Range(Cells(2, 5), Cells(lMaxRows, 5))  
      
        .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC2, 'Fish  List'!C1:C5,2,0)),"""",VLOOKUP(RC2, 'Fish  List'!C1:C5,2,0))"  
        .Copy  
        .PasteSpecial xlPasteValues  
    End With  
      
    With Range(Cells(2, 6), Cells(lMaxRows, 6))  
      
        .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC2, 'Fish  List'!C1:C5,3,0)),"""",VLOOKUP(RC2, 'Fish  List'!C1:C5,3,0))"  
        .Copy  
        .PasteSpecial xlPasteValues  
    End With  
      
    With Range(Cells(2, 7), Cells(lMaxRows, 7))  
      
        .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC2, 'Fish  List'!C1:C5,4,0)),"""",VLOOKUP(RC2, 'Fish  List'!C1:C5,4,0))"  
        .Copy  
        .PasteSpecial xlPasteValues  
    End With  
End Sub

rizvisa1 · Answer

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site  , http://docs.google.com, http://wikisend.com/  , http://www.editgrid.com  etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too

pidge · Answer

Here is a sample data file
https://authentification.site/files/22947760/PidgeDataSample.xls

What I want is in the sheet 'Colour Spots' for each row, I want to find the same fishID the Fish List sheet and then copy paste the river, site and predation for that fishID from Fish List (Sheet) to Colour Spots (sheet).

In other words, I need to fill in site predation and year on the colour spots sheet by finding the proper information on the Fish List sheet.

Does this make more sense?

VBA search column for matching string

3 responses

Similar discussions

Subscribe To Our Newsletter!