VBA: Attach a variable to a range after executing Find command
 

If I execute a find command, is there a way to select a range based on what
is found and store it as a variable? I have done the following:

Cells.Find(What:=Month_Year, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Selection.End(xlDown).Select

Cells(ActiveCell.Row + 1, ActiveCell.Column).Value = "Allocated Month"
Cells(ActiveCell.Row + 1, ActiveCell.Column + 1).Value = "Location
Assigned"
Cells(ActiveCell.Row + 1, ActiveCell.Column + 2).Value = "Assigned Person"
Cells(ActiveCell.Row + 2, ActiveCell.Column).Value = Month_Year
Cells(ActiveCell.Row + 2, ActiveCell.Column + 1).Value = Location
Cells(ActiveCell.Row + 2, ActiveCell.Column + 2).Value = Name

I now want to be able to select everything that was just entered and store
it as a range that can be used in a DSUM formula later on. Any suggestions?

VBA: Attach a variable to a range after executing Find command
 

If you declare c (Dim c As Range) and use:

Set c = Cells.Find(What:=Month_Year, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Then you can do:

If not c Is Nothing
myVar = c.Value
myAdr = c.Address
End If

Which stores the found cell value in myVar and
store the cell address in myAdr. Then

Range(myAdr) = myVar

Would be true.

"Jayne22" wrote:

If I execute a find command, is there a way to select a range based on what
is found and store it as a variable? I have done the following:

Cells.Find(What:=Month_Year, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Selection.End(xlDown).Select

Cells(ActiveCell.Row + 1, ActiveCell.Column).Value = "Allocated Month"
Cells(ActiveCell.Row + 1, ActiveCell.Column + 1).Value = "Location
Assigned"
Cells(ActiveCell.Row + 1, ActiveCell.Column + 2).Value = "Assigned Person"
Cells(ActiveCell.Row + 2, ActiveCell.Column).Value = Month_Year
Cells(ActiveCell.Row + 2, ActiveCell.Column + 1).Value = Location
Cells(ActiveCell.Row + 2, ActiveCell.Column + 2).Value = Name

I now want to be able to select everything that was just entered and store
it as a range that can be used in a DSUM formula later on. Any suggestions?