Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function that will look at letter in a column and return a number | Excel Worksheet Functions | |||
Column() to return a letter instead of a number? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Return number of current sheet | Excel Worksheet Functions | |||
How to return the row number or column letter as a variable? | Excel Programming |