Ok, that works great. I know it's working because if I choose a date I know does not exist, I get the vbExclamation.
To verify the target, I added a bit, Which gives me a Run Time error "Application-defined or object-defined error" here:
The whole code I'm using now is:
Code: Select all
'Find Coresponding sheets
Dim strSiteID As String
Application.ScreenUpdating = False
'Loop through list box to determine wich sheet to pull data from
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSiteID = Me.List_sites2.List(i)
Set ws = Worksheets("" & strSiteID & "")
'Find date value in selected sheet
Dim rngFound As Range
Set rngFound = ws.Columns("A:A").Find(DateValue(Me.datefrom.Text))
If rngFound Is Nothing Then
MsgBox "Date not found!", vbExclamation
Exit Sub
End If
irow = rngFound.Row
'Find Metric value in selected sheet
Set Rng = ws.Rows(1).Find(Me.met1.Text)
If Rng Is Nothing Then
MsgBox "Metric not found!", vbExclamation
Exit Sub
End If
icol = Rng.Column
'Highlight target***Run time error happens now***
ws.Cells(irow, icol).Select
End If
Next i
I also tried to copy the target value back to the form in a texbox, with the same result.
Code: Select all
value_gross.text = ws.cells(irow, icol).value
I'm gonna sleep on it. Very frustrating day. Thanks for all the help Hans.