'Loop to multiple sheets with multiple criteria to get the price
I have a workbook with several worksheets. The main worksheet is the Data worksheet. The search criteria are in the Data worksheet B2,C2 and D2.The other sheets are cross tabs in which the prices are located. The prices I am looking for should be transferred in sheet Data column G2. I stuck with following code.
Dim wks As Worksheet
Dim wksData As Worksheet: Set wksData = Sheets("Data")
Dim lngrow As Long
Dim lngrow2 As Long
Dim lngSpalte As Long
For lngrow = 2 To wksData.Cells(Rows.Count, 2).End(xlUp).Row
Select Case wksData.Cells(lngrow, 2).Value
Case "Standard"
Set wks = Sheets("Standard")
Case "Express Plus"
Set wks = Sheets("Express Plus")
Case "Express Saver"
Set wks = Sheets("Express Saver")
End Select
For lngrow2 = 2 To wks.Cells(Rows.Count, 2).End(xlUp).Row
If Trim(wks.Cells(lngrow2, 2).Value) = Trim(wksData.Cells(lngrow, 3).Value) Then
For lngSpalte = 2 To 10
If Trim(wks.Cells(lngSpalte, 3).Value) = Trim(wksData.Cells(lngrow, 4)) Then
wksData.Cells(lngrow, 7).Value = wks.Cells(lngrow2, lngSpalte).Value
Exit For
End If
Next
End If
Next
Next
Is anyone able to help? Thank you!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
