Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba: attach a variable name to a selected range | Excel Programming | |||
Executing dos command | Excel Programming | |||
Executing a command when clicking on a tab | Excel Programming | |||
Need help using a variable in Range command | Excel Programming | |||
Variable usage in Range Command | Excel Programming |