Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA store location as variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA store location as variable
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA store location as variable
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA store location as variable
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA store location as variable
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA store location as variable
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)" "Jayne22" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet name store in a variable | Excel Programming | |||
Way to store cell location to variable? | Excel Programming | |||
store variable | Excel Programming | |||
How to store current row into variable | Excel Programming | |||
Store variable value for Chart | Excel Programming |