VBA search column for matching string [Solved/Closed]

Posts
3
Registration date
Sunday June 13, 2010
Last seen
June 13, 2010
- - Latest reply:  Juzer - Aug 25, 2016 at 02:44 AM
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.
See more 

3 replies

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

Say "Thank you" 5

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

CCM 4416 users have said thank you to us this month

pidge
Posts
3
Registration date
Sunday June 13, 2010
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
915
0
Thank you
Could you please upload a sample file with sample data etc on some shared site like http://www.speedyshare.com/ , 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
Last seen
June 13, 2010
0
Thank you
Here is a sample data file
http://www.speedyshare.com/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 ***@***