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

 

[JLGWhiz]

a cell address is a string data type. With that in mind, do not use the Set keyword to get the address, since Set is used to initialize Object variables. The code below initializes the variable 'a' with the cell address.

Sub t()
Dim a As String
a = ActiveCell.Address
MsgBox a
End Sub

You are probably havihng trouble because your declarations (Dim statements) do not match the initialization statements for data types.

 

[NdNoviceHlp]

It's kind of hard to tell exactly what U want to do. My take is that U want to find all of the "Regen" in "D" of "list" sheet, get an offset value and then place them somewhere in sheet "Temp". I'm not clear on the last part. Setting Alpha and Beta equal to the same active cell will make them both the same value. The use of selection and active cell is rarely needed and will slow down code execution. Anyways, trial this code to see if the first part is Ok. Post what your desired outcome is. Dave

Option Explicit
Sub test()
Dim Lastrow As Integer, Rng As Range, R As Range
Dim te As String, Oe As String
With Sheets("List")
    Lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
End With
Set Rng = Sheets("List").Range("D1:D" & Lastrow)
For Each R In Rng
If InStr(R, "Regen") Then
te = R.Text
Oe = R.Offset(-1, -3)
End If
MsgBox "te " & te & "Oe " & Oe
Next R
End Sub

 

[VBA_Wonderer]

JLGWhiz - Thank you, this is the information I was looking for and it has resolved the issue I was having with Alpha and Beta.

I have run into one more issue with the following line of the code, where I'm starting another search after the cell address in Beta, for the next cell with Regen.
Selection.Find(What:="Regen", After:=Beta).Activate.
I have made a work around by selecting the Beta cell, and then searching with slightly altered search parameters.

Sheets("CLLI_List").Select
    Range(Beta).Select  [COLOR=#008000]'  start new search after last found cell with Regen (Beta)[/COLOR]
    [COLOR=#ffa07a]Cells.Find[/COLOR](What:="Regen", After:=[COLOR=#ffa07a]ActiveCell, LookIn:=xlValues, LookAt:= _[/COLOR]
[COLOR=#ffa07a]        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _[/COLOR]
[COLOR=#ffa07a]        True, SearchFormat:=False)[/COLOR].Activate
    Beta = ActiveCell.Address   [COLOR=#008000]'for the next search[/COLOR]

I'm still interested to know if there is a way to use a variable in the search parameters, instead of using "After:=ActiveCell".

 

[VBA_Wonderer]

Dave, my plan was to use Alpha as a reference to the first cell found, because I noticed the search would eventually move back to the top of the work sheet after the last Regen cell was found. So if Beta matched Alpha, I would know all instances of Regen were found. I appreciate the code you have given me, and intend on using it in place of mine. I understand how selecting a cell would slow down the code execution, but at this point I need the basic steps as I try to learn VBA. I have been copying an pasting snippets of code for many years, without taking time to really understand what it actually does.
In your code, the line "If InStr(R, "Regen") Then", will this find only an exact match of "Regen", or will it find any instance of regen. The reason I ask is there is another column that has "REGEN" in it. I understand your code only looks in column D, but for future reference I would like to know this.
Thank You for taking the time to help me.

 

[VBA_Wonderer]

Dave, please disregard my question on InStr code. I have just read the explanation of InStr usage in a book, detailing the Binary or Textual comparsion argument. Binary being case sensitive and textual not being case sensitive, the default being Binary if not specified.
Thanks again - Lewis

 

[NdNoviceHlp]

Thank you for posting your outcome. If U have difficulty with finding where and placing you info in another sheet just post your specific requirements. Have a nice day. Dave

 

[JLGWhiz]

JLGWhiz - Thank you, this is the information I was looking for and it has resolved the issue I was having with Alpha and Beta.

I have run into one more issue with the following line of the code, where I'm starting another search after the cell address in Beta, for the next cell with Regen.
Selection.Find(What:="Regen", After:=Beta).Activate.
I have made a work around by selecting the Beta cell, and then searching with slightly altered search parameters.

I'm still interested to know if there is a way to use a variable in the search parameters, instead of using "After:=ActiveCell".

You have to understand what your variable is. In this case, it is the range address and not the range itself. So, you need to use it like.

Cells.Find(What:="Regen", After:=[COLOR=#ffa07a]Range(Beta),[/COLOR] LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate

If you declare your variable as a String data type for a range address ($A$1) then it must be used as a string throughout the code.

Range(myVariable) = "Something"

If you declare your variable as a Range object (Range("A1")) then you use it as a range throughout the code.

myVariable = "Something"

 

[VBA_Wonderer]

You have to understand what your variable is. In this case, it is the range address and not the range itself. So, you need to use it like.

Cells.Find(What:="Regen", After:=[COLOR=#ffa07a]Range(Beta),[/COLOR] LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate

If you declare your variable as a String data type for a range address ($A$1) then it must be used as a string throughout the code.

Range(myVariable) = "Something"

If you declare your variable as a Range object (Range("A1")) then you use it as a range throughout the code.

myVariable = "Something"

JLGWhiz, Your explanation makes perfect sense to me, after I read it a few times. I need every little tidbit of knowledge I can get.
Really appreciate you taking time to respond. Lewis

 

[JLGWhiz]

JLGWhiz, Your explanation makes perfect sense to me, after I read it a few times. I need every little tidbit of knowledge I can get.
Really appreciate you taking time to respond. Lewis

Happy to help, but most of the information I have furnished is available in free on line tutorials. I realize that many users do not have time time for various reasons to use the tutorials, but they are out there if you do have the time. Much better explanations than I give.