Need Help With VBA to Copy Active Cell Address to Range Variable

[VBA_Wonderer]

This is my first post, but I have used code samples from this site for many years. My trust in the code I have "borrowed" has encouraged me to become a member and learn to create my own code. Many thanks to all!

Excel 2010, MS Windows 7 Pro.

I want to copy the address of the active cell to my Range variables (Alpha & Beta), but get only the content of the active cell in Range variables. I have tried different ways of setting the address of the active cell in my range variables, but I get either the contents of the active cell, or a error.

These result in contents of active cell in range variable.
Alpha = ActiveCell
Beta = ActiveCell
Set Alpha = ActiveCell
Set Beta = ActiveCell

These result in Run-time error '91': Object variable or With block variable not set.
Alpha = ActiveCell.Address
Beta = ActiveCell.Address

These result in Compile error: Type mismatch.
Set Alpha = ActiveCell.Address
Set Beta = ActiveCell.Address

These result in Compile error: Argument not optional.
Alpha = ActiveCell.Range
Beta = ActiveCell.Range
Set Alpha = ActiveCell.Range
Set Beta = ActiveCell.Range

Columns("A:A") is a list of offices on both sheets. The list are not identical.
Columns("D:D") is regen type

Sub FindRegenOffices()
' this sub copies the regen type to the TEMP sheet
' where the offices are already listed

Dim Te As String    'Regen Type
Dim Oe As String    'Office
Dim Alpha As Range     ' First cell found with Regen
Dim Beta As Range       ' Previous cell found with Regen

    Sheets("List").Select
    Columns("D:D").Select
    Selection.Find(What:="Regen").Activate
    
    Set Alpha = ActiveCell   'want this to be address of activecell
    Set Beta = ActiveCell     'want this to be address of activecell

    Te = ActiveCell.Text          'copy regen text
    ActiveCell.Offset(-1, -3).Select   
    Oe = ActiveCell.Text          'copy office

WriteIt:     ' loop for writing office to TEMP worksheet in D

    Sheets("TEMP").Select
    Columns("A:A").Select     ' find office (Oe) in column A
    Selection.Find(What:=Oe).Activate
    ActiveCell.Offset(0, 3).Select
    ActiveCell = Te         ' write regen type (Te)
    
    Sheets("List").Select
    Columns("D:D").Select         ' start new search after last found cell with Regen (Beta)
    Selection.Find(What:="Regen", After:=Beta).Activate

    Beta = ActiveCell      'want this to be address of new activecell
    
    If Beta = Not Alpha Then  ' stop loop when first regen cell is found again
        GoTo WriteIt
        Else
    End If
End Sub

Any advice is greatly appreciated. I assume this could be written a different, perhaps more compact way. This is my novice attempt to take the basic steps of manual copy/paste mouse clicks, and make something automated do it the same way. Hopefully, in a short while, I will learn better ways of creating macros. Please, if you would be so kind, help me fix this code and hopefully I will learn from my errors.
Thank You - Lewis

 

[VBA_Wonderer]

Thanks for the help NdNoviceHlp & JLGWhiz. I have been able to rewrite the code into a much shorter SubRoutine. The final working code is below.

Sub FindRegenOffice()
Dim te As String 'Regen Type
Dim Oe As String 'Office
Dim Lastrow As Integer, Rng As Range, R As Range


    With Sheets("List")
        Lastrow = .Range("D" & .Rows.Count).End(xlUp).row
    End With
    
    Set Rng = Sheets("List").Range("D2:D" & Lastrow)
    
    For Each R In Rng
    If InStr(R, "Regen") Then
            te = R.Text
            Oe = R.Offset(-1, -3)
    'MsgBox "te " & te & "Oe " & Oe


        With Sheets("TEMP")
        Cells.Find(What:=Oe, After:=ActiveCell).Offset(0, 3) = te
        End With


    Else
    End If
    Next R
End Sub

 

[NdNoviceHlp]

You are welcome. A solution without using selection or activate. No need for the "Else" in your code. Thanks for posting your outcome. Dave