VBA store location as variable

[Jayne22]

Is there a way to store a range as a variable so that later I can go back to
that location? If so, what do you DIM the variable as?

For example, this is what I have so far:
DIM row1 as Long

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

Selection.End(xlDown).Select
row1 = Cells(ActiveCell.Row + 1, ActiveCell.Column)
(at this point I have statements that insert values into cells near this
location, so I can't redo the same find)

And then later I try to go back to that spot:
Sheets("pivot table").Range(row1).Activate

 

[JLGWhiz]

If you set an object variable like:

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

Then You can use c as your reference point. To use the cell reference.

If Not c Is Nothing Then '<<<Make sure you found data
rng = c.Address '<<<Get the cell reference string
rngVal = c.Value '<<<Get the Value of the found range
End If

Now you can use Range(rng) to take you back to the original found range.
You can use rngVal to call up the original found range value. Both of the
variables will retain their values even if you subsequently change the
the found range value.

 

[Jayne22]

Thanks!
Now what if I want to use c in a dsum formula, along with another value (d)
that I have declared as a range? I'm not sure how to call upon the c and d
variables in the equation.

'Sheets("hours available").Cells(ActiveCell.Row + 1,
ActiveCell.Column).FormulaR1C1 = "=dsum(work_items_database,""work effort
(hours)"", & c & ":" & d")"

I don't think this is right, but do you have any thoughts on how I can
correct this?

 

[JLGWhiz]

You would use the variable you set for the Address of c which was "rng".
That variable returns the found cell with an absolute reverence in $A$1
notation, so in your formula that is entered on the worksheet, it would
appear as an absolute cell reference. See below.


ActiveCell.Column.Formula = "=dsum(work_items_database,""work effort
(hours)"", & rng & ":" & d")"

In any event, going back to your original problem, if you assign the found
cell value to a variable, you can use it anywhere in the code thereafter and
it will retain the same value until you, the programmer, change it.

 

[Jayne22]

I have a couple questions:
1) If I use a value instead of an address, will that still work in the dsum
equation?
2) I think you may be missing a quotation mark in your equation? Where would
that final one go?

Thank you for all your help!

 

[JLGWhiz]

1) DSUM is looking for a range that meets the criteria specified in the third
part of the formula. If you throw in a non-string character, it would
probably tilt.

2) I just copied your original formula and then deleted an extraneous
parenth. But it looks like there might be one too many quotation marks. I
think the one following the d should be removed along with one of the
ampesands. More like this. I am assuming the d represents an A1 notation
style cell reference. Not seeing the rest of your code makes it difficult to
edit the formula, but the ":" indicates you are trying to establish a
criteria range.

ActiveCell.Column.Formula = _
"=dsum(work_items_database,""work effort(hours)"", rng & ":" & d)"