Creating Pivot / Consult

[HansV]

You get that error if the value is not found in the specified range. You can get around it like this:

Code: Select all

  Dim rngFound As Range
  Set rngFound = Worksheets("" & strSiteID & "").Columns("A:A").Find(DateValue(Me.datefrom.Text))
  If rng Is Nothing Then
    MsgBox "Date not found!", vbExclamation
    Exit Sub
  End If
  irow = rng.Row
  Set rng = Worksheets("" & strSiteID & "").Rows(1).Find(Me.met1.Text)
  If rng Is Nothing Then
    MsgBox "Metric not found!", vbExclamation
    Exit Sub
  End If
  icol = rng.Column

[Joseph]

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:

Code: Select all

ws.Cells(irow, icol).Select

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.

[HansV]

Check the values of irow and icol when the error occurs.

[Joseph]

The value does exist within the sheet target. Based on the criteria I'm entering, it should return the value "$3,033.25".

[HansV]

If you single-step through the code, can you see whether rngFound (or rng) is Nothing?

[Joseph]

Yes I can. If I search for a date or metric that does not exist, I get the vbexclamtion. Is that what your asking?

[HansV]

Oh wait - is the worksheet ws the active one when you run

ws.Cells(irow, icol).Select

You can only select cells in the active sheet. But I wonder whether you really need to select the cell, especially in a loop!

[Joseph]

I just used the same code in a different procedure, and it works....
must have something to do with the other loops throwing off the activesheet.

I did try to activate, many different ways. I guess I could have this run on a change event..

Thanks hans.