0

New to stackoverflow and VBA (and to scripting in general to a lesser extent) so I apologise if I've broken any rules or made any silly mistakes.

Currently I have a one dimentional array containing several strings. I want to build a loop that for each string in the array searches all of the columns of sheet1 for the string and then copies the containing rows to the first blank row in sheet2.

The issue is that I can't find a built in subroutine that will allow me to search multiple/all columns for the search term, only ones that look in a single column. I assume there is something built in that does this as it seems like an obvious thing to have- where can I find it? And if you're feeling kind and you have some example code please post it :)

Thanks,
Louis

P.S. If anyone has some VBA guides/documentation that they would like to recommend, please do! My google fu is weak and I've not found much so far.

MrPopinjay
  • 179
  • 4
  • 14
  • This should get you started http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s – Siddharth Rout Apr 15 '13 at 11:39
  • use the `.Find` method. it is a method of the `Range` object. So you can use the method on any range that you want to specify. – mango Apr 15 '13 at 12:37
  • @mango I did consider using the .Find method but the problem I had was that I did not know how to determine how many times I'd need to run the .Find loop in order to find every instance of the search term in the sheet. How would you recommend getting around this? Thanks :) – MrPopinjay Apr 15 '13 at 12:48

1 Answers1

0

This is not really an efficient code, but it should give you a pretty good idea how to use loops (columns, rows, array)
note: if you have some sort of structure in your spreadsheet (for example headers) then you can find the last used column in row - which is more efficient then looping through ALL columns in the sheet.

   Sub doTheJob()
        Application.ScreenUpdating = False
        Dim ws1 As Worksheet, ws2 As Worksheet, arr() As String, str$, i&, j&, k&

        Set ws1 = ThisWorkbook.Sheets(1)
        Set ws2 = ThisWorkbook.Sheets(2)

        str = "this is your string"
        arr = Split(str, " ")

        For i = 1 To ws1.Columns.Count
            For j = 1 To ws1.Cells(Rows.Count, i).End(xlUp).Row
                For k = LBound(arr) To UBound(arr)
                    If StrComp(CStr(ws1.Cells(j, i).Value), arr(k), vbTextCompare) = 0 Then
                        ws1.Rows(j & ":" & j).Select
                        Selection.Copy
                        Dim nxt&
                        nxt = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
                        ws2.Rows(nxt & ":" & nxt).Select
                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    End If
                Next k
            Next j
        Next i
    Application.ScreenUpdating = True
End Sub

Post edit - it should copy entire rows now...

  • This is very helpful, thanks! I was thinking about doing pretty much this but I thought it would be built in and I was unsure as to the syntax. I'm going to fiddle with this a little and then see if I can work out how to make it copy the entire row rather than just that cell. I expect I'll bug you again in a little bit :) – MrPopinjay Apr 15 '13 at 14:27
  • ok, see edit. Let me just ask you one question. What is the point of looping through all columns if you are copying/pasting the entire row? It sounds like you are repeating...and youre going to end up with the same exact rows in wb2 –  Apr 15 '13 at 15:49
  • The point in looping through the columns is so I can search every cell in every column rather than just one column. I can't find any other method to do this. The scope of the search is the entire sheet. – MrPopinjay Apr 15 '13 at 19:27
  • ok. I cant see your data structure, but im trying to visualise it –  Apr 16 '13 at 06:57
  • The data structure is just a 1 dimentional array of single word strings. It's the container for the search terms, not what I'm searching :) – MrPopinjay Apr 16 '13 at 08:35