Hi All,

I have a spin box linked to a cell.
I need to be able to run code that will attach the spin box to a different
cell 13 rows down. The current location of the cell can vary depending on how
many rows are insterted above it, so I cannot simply redefine the spin button
with a fixed cell reference. The new cell can not have a range name.

Thanks,

Squeaky

Can you work with one of these concepts?

Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Find the end of a list? Here are a couple different ways:
this will select the cell. it's not a good practice to select, but just used
as
an illustration here.
range(worksheets("Sheet1").cells(rows.Count,"A").e nd(xlup).address).Select

or if you just want A5 returned:
lastcell = worksheets("Sheet1").cells(rows.Count,"A").end(xlu p).address(0,0)

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Squeaky" wrote:

Hi All,

I have a spin box linked to a cell.
I need to be able to run code that will attach the spin box to a different
cell 13 rows down. The current location of the cell can vary depending on how
many rows are insterted above it, so I cannot simply redefine the spin button
with a fixed cell reference. The new cell can not have a range name.

Thanks,

Squeaky

Hi Ryan,

How do I get this to attach itself to the spin button's linked cell reference?

Squeaky

"ryguy7272" wrote:

Can you work with one of these concepts?

Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Find the end of a list? Here are a couple different ways:
this will select the cell. it's not a good practice to select, but just used
as
an illustration here.
range(worksheets("Sheet1").cells(rows.Count,"A").e nd(xlup).address).Select

or if you just want A5 returned:
lastcell = worksheets("Sheet1").cells(rows.Count,"A").end(xlu p).address(0,0)

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Squeaky" wrote:

Hi All,

I have a spin box linked to a cell.
I need to be able to run code that will attach the spin box to a different
cell 13 rows down. The current location of the cell can vary depending on how
many rows are insterted above it, so I cannot simply redefine the spin button
with a fixed cell reference. The new cell can not have a range name.

Thanks,

Squeaky