Return Current Column Letter and Row Number
 

Hi,

Let's say cell B3 is currently selected. I want VBA to be able to type
in the cell, "=INDEX(B3,-1,0)+7". Cell B2 has a date in it, and I want
cell B3 to contain the date that is 7 days later by using the INDEX
function. However, when I run the VBA, I won't know what specific cell
I'll currently have selected (other than the column, which will always
be B). It will be different every time. Any suggestions about how I
can come up with the "B3" part of the INDEX function?

This is sort of a VBA and a function question, so I hope it can be
answered in this forum.

Thanks,

Carroll Rinehart

Return Current Column Letter and Row Number
 

Carroll wrote:
Hi,

Let's say cell B3 is currently selected. I want VBA to be able to
type in the cell, "=INDEX(B3,-1,0)+7". Cell B2 has a date in it, and
I want cell B3 to contain the date that is 7 days later by using the
INDEX function. However, when I run the VBA, I won't know what
specific cell I'll currently have selected (other than the column,
which will always be B). It will be different every time. Any
suggestions about how I can come up with the "B3" part of the INDEX
function?

This is sort of a VBA and a function question, so I hope it can be
answered in this forum.

If b3 is selected and you want b3 to be b2 + 7:

For Each c In Selection
c.Value = Cells(c.Row - 1, c.Column).Value + 7
Next c

That ought do it. In this way you can select any number of cells and the
code will make the cell equal to the one above it plus 7. You'll get a
runtime error if the cell contains something other than a value though.

Return Current Column Letter and Row Number
 

Hi Carroll, try this:

activecell.value = "=INDEX(" & ActiveCell.Address(0, 0) & ",-1,0)+7"

P.S. Does this function ' =INDEX(B3,-1,0)+7 ' really work?

Return Current Column Letter and Row Number
 

Thanks for your response. I've just noticed a response to an earlier
post that will give me what I need as well:
=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)+7. With this, I'll
have VBA type it in the cell as I need it. It will automatically pick
up the date from the cell above and add 7 to it.

Thank you very much for your time.

Carroll Rinehart

Return Current Column Letter and Row Number
 

Activecell.Value = activecell.Offset(-1,0).Value + 7

--
HTH

Bob Phillips

"Carroll" wrote in message
ps.com...
Hi,

Let's say cell B3 is currently selected. I want VBA to be able to type
in the cell, "=INDEX(B3,-1,0)+7". Cell B2 has a date in it, and I want
cell B3 to contain the date that is 7 days later by using the INDEX
function. However, when I run the VBA, I won't know what specific cell
I'll currently have selected (other than the column, which will always
be B). It will be different every time. Any suggestions about how I
can come up with the "B3" part of the INDEX function?

This is sort of a VBA and a function question, so I hope it can be
answered in this forum.

Thanks,

Carroll Rinehart

Return Current Column Letter and Row Number
 

Yes. That's a very good approach. Rather than having a function
showing up in the cell that displays the correct value, I'll have the
actual value hardcoded in the cell.

Thanks folks!

Carroll