VBA search column for matching string [Solved/Closed]

Report
Posts
3
Registration date
Sunday June 13, 2010
Status
Member
Last seen
June 13, 2010
-
 Juzer -
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.

3 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
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
6
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3998 users have said thank you to us this month

pidge
Posts
3
Registration date
Sunday June 13, 2010
Status
Member
Last seen
June 13, 2010

Brilliant. Sorry. I had my own typos, but once fixed, the code worked perfectly. Thanks for your help and I am removing the sample data set. If someone would like to see it, let me know.
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
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
Posts
3
Registration date
Sunday June 13, 2010
Status
Member
Last seen
June 13, 2010

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?
I am unable to download file, plz send me by email ***@***