sooty8

03-12-2010, 06:55 AM

Hi

Below is the code that solved my original problem - have added another line for the Case.5087 and added 2 text boxes to cover and fill the sheets via the ID number - and with each individual ID it works spot on - the problem is I have now received the csv file for the 400 or so ID's for Case.5087 it means I would have to enter each ID number for Case 5087 is there a way of reading the sheet "CSV" and adding them enbloc. Case.5087 in red.

Private Sub Find_Click()

Dim ws As Worksheet

Dim vecRows As Variant

Dim iRow As Long

Dim i As Long

Set ws = Worksheets("CSV")

With Me

vecRows = Application.Evaluate("IF(TRIM(I2:I500)=""" & Tb1A.Text & """,ROW(I2:I500))")

If .Tb1A.Text <> "" Then

For i = LBound(vecRows) To UBound(vecRows)

If vecRows(i, 1) Then

iRow = vecRows(i, 1)

Select Case ws.Cells(iRow, "H").Value2

Case 4161: .Tb5.Text = ws.Cells(iRow, 10): .Tb6.Text = ws.Cells(iRow, 11)

Case 4021: .Tb7.Text = ws.Cells(iRow, 10): .Tb8.Text = ws.Cells(iRow, 11)

Case 5011: .Tb9.Text = ws.Cells(iRow, 10): .Tb10.Text = ws.Cells(iRow, 11)

Case 4180: .Tb11.Text = ws.Cells(iRow, 10): .Tb12.Text = ws.Cells(iRow, 11)

Case 4048: .Tb13.Text = ws.Cells(iRow, 10): .TB14.Text = ws.Cells(iRow, 11)

Case 4191: .Tb15.Text = ws.Cells(iRow, 10): .Tb16.Text = ws.Cells(iRow, 11)

Case 5073: .Tb17.Text = ws.Cells(iRow, 10): .Tb18.Text = ws.Cells(iRow, 11)

Case 5029: .Tb19.Text = ws.Cells(iRow, 10): .Tb20.Text = ws.Cells(iRow, 11)

Case 4087: .Tb21.Text = ws.Cells(iRow, 10): .Tb22.Text = ws.Cells(iRow, 11)

Case 5042: .Tb23.Text = ws.Cells(iRow, 10): .Tb24.Text = ws.Cells(iRow, 11)

Case 4133: .Tb25.Text = ws.Cells(iRow, 10): .Tb26.Text = ws.Cells(iRow, 11)

Case 5087: .Tb27.Text = ws.Cells(iRow, 10): .Tb28.Text = ws.Cells(iRow, 11)

End Select

End If

Next i

End If

End With

End Sub

Many Thanks

Sooty 8

Below is the code that solved my original problem - have added another line for the Case.5087 and added 2 text boxes to cover and fill the sheets via the ID number - and with each individual ID it works spot on - the problem is I have now received the csv file for the 400 or so ID's for Case.5087 it means I would have to enter each ID number for Case 5087 is there a way of reading the sheet "CSV" and adding them enbloc. Case.5087 in red.

Private Sub Find_Click()

Dim ws As Worksheet

Dim vecRows As Variant

Dim iRow As Long

Dim i As Long

Set ws = Worksheets("CSV")

With Me

vecRows = Application.Evaluate("IF(TRIM(I2:I500)=""" & Tb1A.Text & """,ROW(I2:I500))")

If .Tb1A.Text <> "" Then

For i = LBound(vecRows) To UBound(vecRows)

If vecRows(i, 1) Then

iRow = vecRows(i, 1)

Select Case ws.Cells(iRow, "H").Value2

Case 4161: .Tb5.Text = ws.Cells(iRow, 10): .Tb6.Text = ws.Cells(iRow, 11)

Case 4021: .Tb7.Text = ws.Cells(iRow, 10): .Tb8.Text = ws.Cells(iRow, 11)

Case 5011: .Tb9.Text = ws.Cells(iRow, 10): .Tb10.Text = ws.Cells(iRow, 11)

Case 4180: .Tb11.Text = ws.Cells(iRow, 10): .Tb12.Text = ws.Cells(iRow, 11)

Case 4048: .Tb13.Text = ws.Cells(iRow, 10): .TB14.Text = ws.Cells(iRow, 11)

Case 4191: .Tb15.Text = ws.Cells(iRow, 10): .Tb16.Text = ws.Cells(iRow, 11)

Case 5073: .Tb17.Text = ws.Cells(iRow, 10): .Tb18.Text = ws.Cells(iRow, 11)

Case 5029: .Tb19.Text = ws.Cells(iRow, 10): .Tb20.Text = ws.Cells(iRow, 11)

Case 4087: .Tb21.Text = ws.Cells(iRow, 10): .Tb22.Text = ws.Cells(iRow, 11)

Case 5042: .Tb23.Text = ws.Cells(iRow, 10): .Tb24.Text = ws.Cells(iRow, 11)

Case 4133: .Tb25.Text = ws.Cells(iRow, 10): .Tb26.Text = ws.Cells(iRow, 11)

Case 5087: .Tb27.Text = ws.Cells(iRow, 10): .Tb28.Text = ws.Cells(iRow, 11)

End Select

End If

Next i

End If

End With

End Sub

Many Thanks

Sooty 8