2

I am new to using macros and I need some help. I am parsing scientific data sets with a good mount of rows, so I am trying to pull out only the needed rows, based on what sample data I need. The exact number of values I would search for also changes between each data set.

Goal: Be able to input values onto column A on Sheet3, then use a macro to check if any of those values appear in a specified column on Sheet2, and if so, copy paste entire row onto Sheet1, and stopping at the end of that Column A.

Problem: Using Instr, I am able to either set string2 to a single cell using .cells or manually set values, but if I try to use a range, column, or set of cells, the code doesn't work.

Does Instr allow you to set string2 as multiple cells...??

I am able to set multiple cells if I use "OR", but that ends up being very inelegant, and it also only works if you manually set the same number of values in the code vs on the intended string2 column; e.g.if you have 5 values you want to search, but have 6+ values in the code, it will search the blank and ends up pulling bad unwanted rows.

This is the section of my current test code that I'm having issues with:

Dim wb As Workbook: Set wb = ActiveWorkbook
Dim wsa As Worksheet
Set wsa = wb.Worksheets("Sheet2")
Dim wsb As Worksheet
Set wsb = wb.Worksheets("Sheet3")

a = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

    If InStr(wsa.Cells(i, 2), wsb.Cells(1, 1)) Or _
    InStr(wsa.Cells(i, 2), wsb.Cells(2, 1)) Or _
    InStr(wsa.Cells(i, 2), wsb.Cells(3, 1)) Or _
    InStr(wsa.Cells(i, 2), wsb.Cells(4, 1)) Or _
    InStr(wsa.Cells(i, 2), wsb.Cells(5, 1)) Or _
    InStr(wsa.Cells(i, 2), wsb.Cells(6, 1)) then

        wsa.Rows(i).Copy
        Worksheets("Sheet1").Activate
        b = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet1").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        wsa.Activate
    End If
Next

It works fine as long as I need the same number of values to search, but If i only input <6 values, it ends up pulling random lines --- Sometimes I only need to search for 1 value, sometimes a dozens, so it would be nice to not have to manually edit the code to match the number of the inputted values meant to be searched against as string2.

I tried using various ranges/columns instead of wsb.cells, but it won't work.

If InStr(wsa.Cells(i, 2), wsb.Range("A:A")) Then

If InStr(wsa.Cells(i, 2), wsb.Columns(1)) Then
braX
  • 11,506
  • 5
  • 20
  • 33
Alan
  • 21
  • 1
  • 1
    A well formulated question. `InStr` might not work like that but there are other ways, possibly through memory too. That will work faster. Have a look at arrays, dictionaries, collections and filter, to store lookup and search data. I'm sure you'll have someone helping you out below. Welcome to SO =) – JvdV Nov 25 '19 at 06:10
  • You can do this through a formula as well e.g. On sheet 3 for row 1, insert formula `=ISNUMBER(LOOKUP(2^15,SEARCH(Sheet2!A1:A1000,Sheet3!A1,1)))`. Filter and select rows with `TRUE` and copy them to Sheet 1. You can record a macro for this if you want...`A1000` shall be adjusted to suit... – shrivallabha.redij Nov 25 '19 at 06:36
  • I tried using an array with InStr, but it seems like that doesn't work as well? I guess InStr is limited to singular cells or manually set values only? I tried the ISNUMBER function, but I don't think it works? It seems to just return TRUE if there's anything in column A of Sheet 3? Doesn't ISNUMBER also only work for numbers? Sometimes the data set and the terms that I need to search could be names/words. Essentially, are there any functions that I can use with macros for... INPUT LIST -> SEARCH IF ANY OF THE VALUES APPEAR IN ANOTHER LIST – Alan Nov 27 '19 at 06:31

0 Answers0