Modifying the column or row property of a variable?

Hopefully simply done; I'm looking to change the column of a range variable directly, rather than (re-)setting the whole thing with ...=workbooks().worksheet().range("X1") or ...cells(a,b) or selection etc.

most simply described as (non working!);

Please Login or Register  to view this content.

I've tried numerous confudgerations of this.

TIA. :-)

Thanks for the input, both;

Originally Posted by HaHoBe

If you want to offset from the existing range use the code supplied by :) Sixthsense :). But anyhow I would no rely on a Selection being made prior to running a macro.

That's part of the problem; although the variable WILL have a range, I can't rely on there being a selection - that's why I was looking for an option to modify a single parameter without using .range, .cells or a selection.
The other two suggestions both have their own issues;
Firstly, setting columns(8) seems to assign just a column reference to the variable, overwriting where I would preserve other parameters such as the row and sheet reference.
And the .resize AFAIK is used to define a range from a cell. Even if I was to use .offset, it would be relative, rather than the exact column=8 I'm hoping for.

I guess; worst case, I'll try calculating an .offset or set a new selection (without selecting!). I'd still prefer to edit a single parameter rather than redefine the whole variable.

Hmm.ok; Example:
Original:
NewSerialEntry has properties of row=2, column=5, value=12345, worksheet=worksheet/sheet1 (and more...)
after transmorgification; modifying column: (in this case, to value of 8)
NewSerialEntry has properties of row=2, column=8, value=?, worksheet=worksheet/sheet1 (and possibly more...)

ie. JUST the column has been changed to a specific value, retaining row and sheet references.

I guess the value would be updated too, but that's not important.
I'm looking at the offset possibility, but the start value could be anything from 7 to 170+

Thanks for looking! :-)

What I've come up with:
Set NewSerialEntry = NewSerialEntry.Offset(, (8 - NewSerialEntry.Column))
Does the job!

For some reason, I couldn't work out that subtracting the current column would give me the starting point!

Any better way? Actually simple enough, but may as well ask...

Originally Posted by shg

or

Please Login or Register  to view this content.

I thought for a moment; "ah; that's it!", but no; using this resets the range to a new selection;
If a different sheet is active, a completely different cell to that intended is referenced. Using the .offset preserves the worksheet.
Likewise; modifying doesn't have the intended effect!
Set NewSerialEntry= NewSerialEntry.Cells(NewSerialEntry.Row, "H")
Set NewSerialEntry.Cells = NewSerialEntry.Cells(NewSerialEntry.Row, "H")