VBA search column for matching string
Solved/Closed
pidge
Posts
3
Registration date
Sunday June 13, 2010
Status
Member
Last seen
June 13, 2010
-
Jun 13, 2010 at 08:30 PM
Juzer - Aug 25, 2016 at 02:44 AM
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.
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.
Related:
- Excel vba find string in column
- Vba select case string contains - Guide
- Number to words in excel formula without vba - Guide
- Gif in excel - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Excel count occurrences of string in column - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 13, 2010 at 09:21 PM
Jun 13, 2010 at 09:21 PM
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 13, 2010 at 08:47 PM
Jun 13, 2010 at 08:47 PM
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
Posts
3
Registration date
Sunday June 13, 2010
Status
Member
Last seen
June 13, 2010
Jun 13, 2010 at 08:57 PM
Jun 13, 2010 at 08:57 PM
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?
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?
Jun 13, 2010 at 11:05 PM