loop multiple values in excel vba

Question

The code below will search the value in cell H2 and return values related to it. I need to have H2:H50 searched, and return all values related to them.

Sub findrate()

Dim STYLE As Variant
Dim finalrow As Integer
Dim i As Integer
Dim ctrSearchRow As Integer
Dim emstring As String

Sheets("Rate").Range("I6:N20").ClearContents

STYLE = Sheets("Rate").Range("H2").Value
finalrow = Sheets("Sheet1").Range("A31844").End(xlUp).Row

For i = 2 To finalrow
   If Cells(i, 1) = STYLE Then
        Range(Cells(i, 1), Cells(i, 6)).Copy
        Range("H31844").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
            End If
        End If
    Next ctrSearchRow
Next i


End Sub

Change the column in `Cells(i, 1)` to `Cells(i, 8)` ? Also there's an End if too many and a stray Next in there it looks like. — Christofer Weber, Jul 13 '21 at 19:07
Does this answer your question? [Search and return multiple values using excel VBA](https://stackoverflow.com/questions/68353784/search-and-return-multiple-values-using-excel-vba) — cybernetic.nomad, Jul 13 '21 at 19:09
As @christofer-weber noted, you need to remove the `End If` and the `Next ctrSearchRow` below it. Also, you really need to check what sheets you are referring to. You refer to `Sheets("Rate")` and `Sheets("Sheet1")`, but then you do not specify the sheet in the For loop. — GoodJuJu, Jul 13 '21 at 20:25

score 0 · Answer 1 · answered Jul 13 '21 at 22:08

Your code was a bit hard to follow, but I think this should work. The code now loops through the H Column on the Rate WS, pulls the STYLE from the row's H column, and searches the A column of the Sheet1 worksheet for the first row that matches the Style. I think this is what you were looking for; not sure which worksheet you wanted values to be pasted into and which worksheet you wanted to search for matches.

Also, if Sheet1.Cells(whateverRowTheLoopsOn, ColA) matches RateWS.Cells(whateverRowTheOtherLoopsOn, ColH), copying Col A from Sheet1 into Col I of RateWS will be redundant - You'll have "TheStyle" in H and "TheStyle" in I.

   Sub findrate()

       Dim STYLE        As Variant
       Dim finalrow     As Long 'Changed to Long from Int
       Dim i            As Long 'Changed to Long from Int
       Dim k            As Long 'New declaration
       Dim ctrSearchRow As Long 'Changed to Long from Int
       Dim emstring     As String 'Not Used - Delete this
       Dim rateWS       As Worksheet 'New declaration
       Dim searchWS     As Worksheet 'New declaration

       'Setting worksheet objects to make the following code easier to digest
       set rateWS = Application.ThisWorkbook.Worksheets("Rate")
       set searchWS = Application.ThisWorkbook.Worksheets("Sheet1")

       rateWS.Range("I6:N20").ClearContents

       finalrow = rateWS.Cells(Rows.Count, 8).End(xlUp).Row 'Changed to Col H and looking at Rate WS

       ctrSearchRow = searchWS.Cells(Rows.Count, 8).End(xlUp).Row 'New setting

       For i = 2 To finalrow
           STYLE = rateWS.cells(i, 8).Value
           For k = 2 to ctrSearchRow
               If searchWS.Cells(k, 1) = STYLE Then
                   searchWS.Range("A" & k & ":F" & k).Copy
                   rateWS.Range("I" & i & ":N" & i).PasteSpecial xlPasteFormulasAndNumberFormats
                   Exit For
               End If
           Next k
       Next i
   End Sub

I've taken your code and it worked, but a style has different dimensions and sizes so in that case, I need the redundant data to be pulled as well. What changes should I make? — Eugene0913, Jul 14 '21 at 04:18
If the dimensions are different in searchWS, let's say the Style is 10 columns rather than six as defined in your example, you could find the last filled column using something like: lCol = Cells(1, Columns.Count).End(xlToLeft).Column — RLee, Jul 14 '21 at 17:00
Then, you can [convert that number into a corresponding column letter and use it in the Copy line of code.](https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter) - You'll also need to do a bit of addition for the Paste line of code to find the corresponding column letter in RateWS. — RLee, Jul 14 '21 at 17:06

loop multiple values in excel vba

1 Answers1