Hi all.
Hope everyone is having as great a day as I am.....Or better... 8-)
In one of my macros, I'm using:

myRng1.EntireRow.Columns(8).Offset(1, 0)

While it's working good, I've come to realize that with the columns(), I'd
like it to be where I choose it, and not specifically at column H.
There've been a few instances where I have data in H, and it does what I've
told it--in the code-- thus overwriting that data, and in reality, that's not
what I want.

So, my question, what would it take to generically place the initial data at
my location/column of choice?

Thank you.
Best.

You've set a range (myRng1) to something, but you're referencing the entire
row of that range, but you want only one column of that row (which then
becomes a reference to one cell), but you want the cell one row below that
cell?? It seems kind of a round-a-bout way to point to a single cell. What
exactly are you trying to do? Post more code. There's got to be a simpler
way to do what you want.

"SteveDB1" wrote:

Hi all.
Hope everyone is having as great a day as I am.....Or better... 8-)
In one of my macros, I'm using:

myRng1.EntireRow.Columns(8).Offset(1, 0)

While it's working good, I've come to realize that with the columns(), I'd
like it to be where I choose it, and not specifically at column H.
There've been a few instances where I have data in H, and it does what I've
told it--in the code-- thus overwriting that data, and in reality, that's not
what I want.

So, my question, what would it take to generically place the initial data at
my location/column of choice?

Thank you.
Best.

Your question is a little ambiguous but try this scenariao:

myColVar = InputBox("enter a column number", "Column")
Cells(myRng1.Row, myColVar).Offset(1, 0)

I might have misinterpreted what you were attempting with your code snippet.


"SteveDB1" wrote:

Hi all.
Hope everyone is having as great a day as I am.....Or better... 8-)
In one of my macros, I'm using:

myRng1.EntireRow.Columns(8).Offset(1, 0)

While it's working good, I've come to realize that with the columns(), I'd
like it to be where I choose it, and not specifically at column H.
There've been a few instances where I have data in H, and it does what I've
told it--in the code-- thus overwriting that data, and in reality, that's not
what I want.

So, my question, what would it take to generically place the initial data at
my location/column of choice?

Thank you.
Best.

To both Charlie, and JLG,

Sorry about the ambiguity. It wasn't intentional.

I'm asking the user to pick a range of cells (which I'll call- a header),
all on a single row, 6 columns in width. I then merge that first row (and
place a text string in that merged grouping), and offset one row down, and
paste a string of text in columns n, n+1, n+2, ..., n+5. Once this is
complete, I then format the range chosen with borders, centering, etc....
After that I place a series of "active" equations in the cells beneath where
the text strings are located. I.e., they are tied to user input, and cell
addresses.

As I thought about this further, I tried something that works.

I.e., I just answered my own question.

I've changed the

myRng1.EntireRow.Columns(8).Offset(1, 0)

to

myRng1.Offset(1, 0)

however, at this point, I'd like to get a better understanding of .rows(),
and .columns().

The offset seems clear enough to me, but those two are not real clear in my
mind.
What I've read in the help file says that the numeric value in the
parenthesis is suppose to set the row, or column number. So that if I choose
row(1) it should pick row 1, and not allow me to change it to another row,
yet if I understand what I'm doing, I'm able to set myRng1.row(1) to be the
first row that I choose, regardless of what the actual row number was.

eg,
Set myRng1 = Application.InputBox(Prompt:="Select range for APN Tally Header
location", Type:=8).Areas(1).Rows(1)

I then have it select the range of my choosing.
myrng1.select
I then set myRng1 to myRng2

Set myRng2 = myRng1.Rows(1).Offset(1, 0)
'I've just changed this to myRng1.Offset(1, 0)

And then go on about my business within the macro.

I'm guessing at this point that because I set myRng1 with a user input that
the "row" I chose in my range selection now becomes the first row (almost
like setting a new zero point), and it moves on from there.

If you guys could give me something beyond the Excel help file to read on
regarding .rows(), and .columns(), that'd be great.
Thanks.
Best.



"JLGWhiz" wrote:

Your question is a little ambiguous but try this scenariao:

myColVar = InputBox("enter a column number", "Column")
Cells(myRng1.Row, myColVar).Offset(1, 0)

I might have misinterpreted what you were attempting with your code snippet.


"SteveDB1" wrote:

Hi all.
Hope everyone is having as great a day as I am.....Or better... 8-)
In one of my macros, I'm using:

myRng1.EntireRow.Columns(8).Offset(1, 0)

While it's working good, I've come to realize that with the columns(), I'd
like it to be where I choose it, and not specifically at column H.
There've been a few instances where I have data in H, and it does what I've
told it--in the code-- thus overwriting that data, and in reality, that's not
what I want.

So, my question, what would it take to generically place the initial data at
my location/column of choice?

Thank you.
Best.