VBA search column for matching string [Solved/Closed]

pidge 3 Posts Sunday June 13, 2010Registration date June 13, 2010 Last seen - Jun 13, 2010 at 08:30 PM - 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 

5 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 13, 2010 at 09:21 PM
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

Thank you, rizvisa1 5

Something to say? Add comment

CCM has helped 1808 users this month

pidge 3 Posts Sunday June 13, 2010Registration date June 13, 2010 Last seen - Jun 13, 2010 at 11:05 PM
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 13, 2010 at 08:47 PM
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
pidge 3 Posts Sunday June 13, 2010Registration date June 13, 2010 Last seen - Jun 13, 2010 at 08:57 PM
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 ***@***