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

 

[fonk]

Hi Norie,
Finally worked a solution, not very elegant with the selects but at least it is doing what I want. I know it will be slower the more dates are added but at least it works. Code below.

Range("Z3").Select
FinalRow = Range("Z65536").End(xlUp).Row
For i = 3 To FinalRow
If ActiveCell.Value = Val(tb_SelJobID.Value) And ActiveCell.Offset(, -2) = Val(TbSelYr.Value) Then
        If Val(tb_SelJobID.Value) <> ActiveCell.Value Then
  Else
       ActiveCell.Offset(, -3).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
End If
End If
If ActiveCell.Value = Val(tb_SelJobID.Value) And ActiveCell.Offset(, -7) = Val(TbSelYr.Value) Then
        If Val(tb_SelJobID.Value) <> ActiveCell.Value Then
  Else
       ActiveCell.Offset(, -8).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
End If
End If
If ActiveCell.Value = Val(tb_SelJobID.Value) And ActiveCell.Offset(, -12) = Val(TbSelYr.Value) Then
        If Val(tb_SelJobID.Value) <> ActiveCell.Value Then
  Else
       ActiveCell.Offset(, -13).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
End If
End If
  If ActiveCell.Value = Val(tb_SelJobID.Value) And ActiveCell.Offset(, -17) = Val(TbSelYr.Value) Then
        If Val(tb_SelJobID.Value) <> ActiveCell.Value Then
  Else
       ActiveCell.Offset(, -18).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
End If
End If
 If ActiveCell.Value = Val(tb_SelJobID.Value) And ActiveCell.Offset(, -22) = Val(TbSelYr.Value) Then
        If Val(tb_SelJobID.Value) <> ActiveCell.Value Then
  Else
       ActiveCell.Offset(, -23).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
End If
End If
ActiveCell.Offset(1).Select
Next i

Cheers, Dave

 

[Norie]

Dave

Sorry I couldn't get back to you.

That code looks fine but you're probably right about Select.

I could give you a few pointers on that if you want.

For example, try using a range variable.

Dim rng as Range
Dim FinalRow As Long
Dim I As Long
 
   FinalRow = Range("Z65536").End(xlUp).Row
 
   For I = 3 To FinalRow
 
      Set rng = Range("Z" & I)
 
       If rng.Value = Val(tb_SelJobID.Value) And rng.Offset(, -2) = Val(TbSelYr.Value) Then
           If Val(tb_SelJobID.Value) <> rng.Value Then
               ' do nothing
           Else
               rng.Offset(, -3).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
           End If
 
           '  rest of If/EndIfs, replacing ActiveCell with rng.
      End If
 
   Next I

 

[fonk]

Hi Norie,
Thanks for the Range Variable, much better solution, I've learnt a lot out of this one. Thanks for your time, patience and advice.

Cheers, Dave