Define a cell containing specific text as range

[Rainmanne]

I've got two slightly different templates where I need to fill right a formula based on the template selected in the userform. I am thinking to simplify it and instead using If Else structure to use specific text to define a start cell. Currently it looks like this (it's a part of the code):

Dim finstart As Range
Dim endcell As Range, startcell As Range
Dim yearsno As Range
Dim numrowsadj As Integer
Dim cfyearsno As Range
Dim numrows As Integer
Dim numrowscf As Integer

If UserForm1.radioift.Value = True Then
    With ThisWorkbook.Sheets("FS")
        Set startcell = .Range("D1")
        Set endcell = Cells(Range("D" & Rows.Count).End(xlUp).Row, 3 + numrows + numrowsadj)
        Set finstart = .Range(startcell.Address & ":" & endcell.Address)
        finstart.FillRight
    End With
Else
    With ThisWorkbook.Sheets("FS")
    Set startcell = .Range("F1")
    Set endcell = Cells(Range("F150").End(xlUp).Row, 5 + numrows + numrowsadj)
    Set finstart = .Range(startcell.Address & ":" & endcell.Address)
    finstart.FillRight
End With
End If

Basically I want to set startcell to the cell after the cell containing a word "Item". Then define the endcell as the last non-blank cell in the column and then select and fill right by a number of years. The cell containing "Item" if either C1 or E1. I just cannot figure out how to set startcell using the text in the cell. Could anyone help?

 

[Snakehips]

@Rainmanne Maybe similar to below?
Have used LIKE in case cell contains string containing "Item, not just the string "Item" ??
Not tested and please forgive if I have misunderstood.

With ThisWorkbook.Sheets("FS")

    If Range("C1") Like ("*" & ("Item") & "*") Then
    
            Set startcell = .Range("D1")
            Set endcell = Cells(Range("D" & Rows.Count).End(xlUp).Row, 3 + numrows + numrowsadj)
            Set finstart = .Range(startcell.Address & ":" & endcell.Address)
            finstart.FillRight
        
    ElseIf Range("E1") Like ("*" & ("Item") & "*") Then
    
            Set startcell = .Range("F1")
            Set endcell = Cells(Range("F150").End(xlUp).Row, 5 + numrows + numrowsadj)
            Set finstart = .Range(startcell.Address & ":" & endcell.Address)
            finstart.FillRight
    
    End If
End With

 

[Rainmanne]

@Rainmanne Maybe similar to below?
Have used LIKE in case cell contains string containing "Item, not just the string "Item" ??
Not tested and please forgive if I have misunderstood.

With ThisWorkbook.Sheets("FS")

    If Range("C1") Like ("*" & ("Item") & "*") Then
   
            Set startcell = .Range("D1")
            Set endcell = Cells(Range("D" & Rows.Count).End(xlUp).Row, 3 + numrows + numrowsadj)
            Set finstart = .Range(startcell.Address & ":" & endcell.Address)
            finstart.FillRight
       
    ElseIf Range("E1") Like ("*" & ("Item") & "*") Then
   
            Set startcell = .Range("F1")
            Set endcell = Cells(Range("F150").End(xlUp).Row, 5 + numrows + numrowsadj)
            Set finstart = .Range(startcell.Address & ":" & endcell.Address)
            finstart.FillRight
   
    End If
End With

Hi, thanks a lot for that. I had something slightly different in mind. Something like

With ThisWorkbook.Sheets("FS")
            Set startcell = (a cell with text "Item")
            Set endcell = Cells(Range("D" & Rows.Count).End(xlUp).Row, 3 + numrows + numrowsadj)
            Set finstart = .Range(startcell.Address & ":" & endcell.Address)
            finstart.FillRight
End With

So I do not know how to set the start cell.
The start cell contains the string Item, there is no other text there.