Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto a dynamic cell address within a macro
My workbook is formula driven and the tables will dynamically expand or
contract with the size of the data. I have a need to move to the next available row before starting the next subroutine. I have created a formula that computes the correct cell address but I don't know how to structure a goto command within the macro to use it. Can you help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto a dynamic cell address within a macro
If you know the top of the data
assume A1 Dim rng as Range set rng = Range("A1").End(xldown).Offset(1,0) rng.Select or if there could be empty cells in column A Dim rng as Range set rng = Cells(rows.count,1).End(xlup).Offset(1,0) rng.select are possibilities -- Regards, Tom Ogilvy "blkane" wrote in message ... My workbook is formula driven and the tables will dynamically expand or contract with the size of the data. I have a need to move to the next available row before starting the next subroutine. I have created a formula that computes the correct cell address but I don't know how to structure a goto command within the macro to use it. Can you help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto a dynamic cell address within a macro
Thanks Tom,
Unfortunately, going to the top of the range and hitting "end" "pagedown" doesn't work. I'm copying a named range from another worksheet. The named range contains formulas where data is visible only if a value exists. When I copy the range to my worksheet, the cells that appeared blank are messing me up. When I hit "end", "pagedown", it goes to the row that corresponds to the end of the named range instead of the end of the visible data. Hope that's not too confusing. I created a formula that determines the cell address of where I need to navigate. This address will always change each time the workbook is used. I used "Application.Goto Reference:=" to go to the formula. What I need is a command that will look at the value of the formula to obtain the cell address and go to it. Any other thoughts? "Tom Ogilvy" wrote: If you know the top of the data assume A1 Dim rng as Range set rng = Range("A1").End(xldown).Offset(1,0) rng.Select or if there could be empty cells in column A Dim rng as Range set rng = Cells(rows.count,1).End(xlup).Offset(1,0) rng.select are possibilities -- Regards, Tom Ogilvy "blkane" wrote in message ... My workbook is formula driven and the tables will dynamically expand or contract with the size of the data. I have a need to move to the next available row before starting the next subroutine. I have created a formula that computes the correct cell address but I don't know how to structure a goto command within the macro to use it. Can you help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto a dynamic cell address within a macro
Terms like I create a formula and I need to look at the value of the formula
just can't be dealt with. Its like playing I'm thinking of a number. where is the formula, what is the formula, what does it produce (an example). say the formula is on sheet1 in cell Z36 and it displays a string like Sheet1!F22 Then you can do application.Goto Reference:=Range(Worksheets("sheet1").Range("Z36") .Text) -- Regards, Tom Ogilvy "blkane" wrote in message ... Thanks Tom, Unfortunately, going to the top of the range and hitting "end" "pagedown" doesn't work. I'm copying a named range from another worksheet. The named range contains formulas where data is visible only if a value exists. When I copy the range to my worksheet, the cells that appeared blank are messing me up. When I hit "end", "pagedown", it goes to the row that corresponds to the end of the named range instead of the end of the visible data. Hope that's not too confusing. I created a formula that determines the cell address of where I need to navigate. This address will always change each time the workbook is used. I used "Application.Goto Reference:=" to go to the formula. What I need is a command that will look at the value of the formula to obtain the cell address and go to it. Any other thoughts? "Tom Ogilvy" wrote: If you know the top of the data assume A1 Dim rng as Range set rng = Range("A1").End(xldown).Offset(1,0) rng.Select or if there could be empty cells in column A Dim rng as Range set rng = Cells(rows.count,1).End(xlup).Offset(1,0) rng.select are possibilities -- Regards, Tom Ogilvy "blkane" wrote in message ... My workbook is formula driven and the tables will dynamically expand or contract with the size of the data. I have a need to move to the next available row before starting the next subroutine. I have created a formula that computes the correct cell address but I don't know how to structure a goto command within the macro to use it. Can you help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto a dynamic cell address within a macro
Tom,
Thanks. I guess I am asking it to have the "I'm thinking of a number" intelligence. I'll have to think of a plan b. "Tom Ogilvy" wrote: Terms like I create a formula and I need to look at the value of the formula just can't be dealt with. Its like playing I'm thinking of a number. where is the formula, what is the formula, what does it produce (an example). say the formula is on sheet1 in cell Z36 and it displays a string like Sheet1!F22 Then you can do application.Goto Reference:=Range(Worksheets("sheet1").Range("Z36") .Text) -- Regards, Tom Ogilvy "blkane" wrote in message ... Thanks Tom, Unfortunately, going to the top of the range and hitting "end" "pagedown" doesn't work. I'm copying a named range from another worksheet. The named range contains formulas where data is visible only if a value exists. When I copy the range to my worksheet, the cells that appeared blank are messing me up. When I hit "end", "pagedown", it goes to the row that corresponds to the end of the named range instead of the end of the visible data. Hope that's not too confusing. I created a formula that determines the cell address of where I need to navigate. This address will always change each time the workbook is used. I used "Application.Goto Reference:=" to go to the formula. What I need is a command that will look at the value of the formula to obtain the cell address and go to it. Any other thoughts? "Tom Ogilvy" wrote: If you know the top of the data assume A1 Dim rng as Range set rng = Range("A1").End(xldown).Offset(1,0) rng.Select or if there could be empty cells in column A Dim rng as Range set rng = Cells(rows.count,1).End(xlup).Offset(1,0) rng.select are possibilities -- Regards, Tom Ogilvy "blkane" wrote in message ... My workbook is formula driven and the tables will dynamically expand or contract with the size of the data. I have a need to move to the next available row before starting the next subroutine. I have created a formula that computes the correct cell address but I don't know how to structure a goto command within the macro to use it. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Assignment of a Cell Address In Formula | Excel Worksheet Functions | |||
Goto a specific cell in a macro | Excel Discussion (Misc queries) | |||
Using Dynamic Cell Address As Formula Location | Excel Worksheet Functions | |||
Dynamic flexible cell address | Excel Discussion (Misc queries) | |||
Goto a dynamic cell address within a macro | Excel Discussion (Misc queries) |