Use one Set statement to Set second statement

[fonk]

Hi all,
I am attempting to use a previously Set variable as part of the next Set statement, pretty unsucessfully at present.
My purpose is trying to look up

tb_SelJobID.Value

from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if

TbSelYr.Value

matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.

Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. Any help appreciated.
I have cut down the following code to display where the problem areas are, Found1 thru 5.

Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
If cboEmpNme.Value = "Select Employee..." Or cboEmpNme.Value = "" Or tb_SelJobID.Value = "" Then 'Warning if no employee or Job No. selected, exits sub with message
Dim response As VbMsgBoxResult
    response = MsgBox("Select Employee... Or Job ID is not a valid selection", vbOKOnly)
    If response = vbOK Then
        Exit Sub
End If
End If
               FinalRow = Range("Z65536").End(xlUp).Row + 3
For i = 3 To FinalRow
Set Found10 = Cells.Find(what:=tb_SelJobID.Value, LookIn:=xlValues, lookat:= _
    xlWhole, MatchCase:=False)
  If Found10 Is Nothing Then GoTo Line17
 
 
  Set Found1 = Found10.Offset(, -2).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)
        If Found1 Is Nothing Then
  Else
               Found1.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
  End If
 
  Set Found2 = Found10.Offset(, -7).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)
        If Found2 Is Nothing Then
  Else
                 Found2.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
  End If
 
  Set Found3 = Found10.Offset(, -12).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)
        If Found3 Is Nothing Then
  Else
                  Found3.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
  End If
 
  Set Found4 = Found10.Offset(, -17).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)
        If Found4 Is Nothing Then
  Else
                   Found4.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
  End If
 
  Set Found5 = Found10.Offset(, -22).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)
        If Found5 Is Nothing Then
  Else
                   Found5.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
  End If
Line17:
Next i
Range("AD4").Select
        Range("AD4").Value = "0"
               strDate = Format(Text)
 
         ActiveCell.FormulaR1C1 = "=SUM(R[-2]C[-27],R[-2]C[-22],R[-2]C[-17],R[-2]C[-12],R[-2]C[-7])"
Line70:
      Cells.Columns.AutoFit
              Application.ScreenUpdating = True
 
End Sub

Any guidance appreciated.
Regards Dave

Windows 7 with Excel 2010

 

[Norie]

Dave

What do you mean doesn't recognise Found10?

 

[fonk]

Hi Norie,
Once Found10 is Set a mouse hover over it displays the value i.e 3028

Set [COLOR=red][B]Found10[/B][/COLOR] = Cells.Find(what:=tb_SelJobID.Value, LookIn:=xlValues, lookat:= _
    xlWhole, MatchCase:=False)
 If [B][COLOR=red]Found10[/COLOR][/B] Is Nothing Then GoTo Line17

Stepping through the code in the Set Found1 the mouse hover over Found10 displays nothing, hovering over the TbSelYr value displays the year value i.e 2011.
and Found 1 skips straingt to the EndIf when stepping through.

 Set Found1 = [B][COLOR=red]Found10[/COLOR][/B].Offset(, -2).Find(what:=[COLOR=red][B]TbSelYr[/B][/COLOR], LookIn:=xlValues, lookat:=xlWhole)
        If Found1 Is Nothing Then
  Else
               Found1.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
  End If

Cheers, Dave
Windows 7 with Excel 2010

 

[Norie]

Dave

The reason nothing is shown when you hover over is probably because you have an entire piece of code, ie Find, after Found10.

That doesn't mean something's happened to Found10.

Try adding a watch on Found10 by selecting it, right clicking and selecting Add Watch.

You should now be able to see it's value throughout the code.

Oh, and the reason it's skipping to the End If could just be because nothing was found.

 

[fonk]

Norie,
Add Watch value was present but in Grey text not Black text as Found1, Found2 and FinalRow were. This value did not change throughout.

There is a drop down list (+) only beside Found10 in the Add Watch Expression column.

Type for Found10 is Variant/Object/Range

Type for Found1 and Found 2 is Variant/Object

FinalRow value was 9 which is correct
Found1 thru Found5 value changed from empty to nothing when stepping through.
Putting the hyphen after Found10 in the following line for Found1 thru Found5 allows me to see the value when mouse over as you have explained and the Found10 value is still greyed out in the Add Watch value.

The Found1 thru Found5 displays the same value Add Watch value as Found10 as expected, also greyed out with the + sign beside it.

Set Found1 = Found10 '.Offset(, -2).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)

Cheers, Dave
Windows 7 with Excel 2010

 

[fonk]

Hi Norie,
Just looked at the Add Watch for the Offset in the following line

Set Found1 = Found10.Offset(, -2).Find(what:=TbSelYr, LookIn:=xlValues, lookat:=xlWhole)

and the Add Watch value is "Expression Not Defined In Context"<EXPRESSION Context in Defined Not> That would explain my problem, now at least I know where to start. Thanks for the heads up with the Add Watch, never used it before.

Cheers, Dave

Windows 7 with Excel 2010

 

[Norie]

Dave

What exactly did you add as a watch?

Was it just Found10?

If it was and there's dropdown it's because Found10 is a range object.

When the code starts there won't be a dropdown, as it will be empty but when you actually assign it to a range you'll get the dropdown.

 

[fonk]

Norie,

What exactly did you add as a watch?

Found10

Was it just Found10?

Initially, I then added Found1, Found2 and Offset as watches out of curiosity as a comparison.

If it was and there's dropdown it's because Found10 is a range object.

Have Found10 Dim'd As Range

When the code starts there won't be a dropdown, as it will be empty but when you actually assign it to a range you'll get the dropdown.

Yes that's right, only when Found10 is Set does the drop down appear.

Cheers, Dave

 

[Norie]

Dave

So it's showing what you want to see?

 

[fonk]

Norie,

So it's showing what you want to see?

Yes showing the correct value.

My post at 1:48PM I thought may be the problem but I looked at Offset in the Watch window in code on another Cmd Button just then and it shows the same "Expression Not Defined In Context" , I'm assuming it is not a value it can watch.

Cheers, Dave