Conditional column selection
 

set rng = Range("B2").End(xltoRight).offset(0,-1)
if rng.HasFormula then
set rng = rng.offset(0,1)
End if

General approach - adjust to your specific situation.

--
Regards,
Tom Ogilvy


"AG" wrote in message
...
I have a monthly spreadsheet setup such that columns A & B are text,
column C is January, column D is February, column E is March, BUT column F
are quarterly totals formulas and so on ending at column S which are year to
date totals formulas. Entries begin in row 2.

On occasion I need to overwrite the monthly entries.


If I want to overwrite the 2nd or 3rd month of a quarter, no problem, I
would use the End Method.

I'd select cell A2 and use the End Method
Range ("B2") .End(xlTo Right) .Select
And select the previous column.

I run into a problem when I want to overwrite the 1st month of a quarter's
entries as the End Method less 1 column to the left puts me in a quarterly
totals column.

How do I prevent overwriting any of the quarterly columns?

Thanks I advance.

Al

Conditional column selection
 

Very clean; thanks.
The solution exposed me to an entire new set of tools!

Can I then declare the resultant column as a variable AND call/reference it to select other similar similarly setup sheets within the same workbook?

Such that:
set rng = Range("B2").End(xltoRight).offset(0,-1)
if rng.HasFormula then
set rng = rng.offset(0,1)
End if

Dim Acol As Range
Acol = ActiveCell.Column

€˜ do the required work and then

Sheets ("Sheet 2").Select
Acol.Select
€˜ do the required work


"Tom Ogilvy" wrote:

set rng = Range("B2").End(xltoRight).offset(0,-1)
if rng.HasFormula then
set rng = rng.offset(0,1)
End if

General approach - adjust to your specific situation.

--
Regards,
Tom Ogilvy


"AG" wrote in message
...
I have a monthly spreadsheet setup such that columns A & B are text,
column C is January, column D is February, column E is March, BUT column F
are quarterly totals formulas and so on ending at column S which are year to
date totals formulas. Entries begin in row 2.

On occasion I need to overwrite the monthly entries.


If I want to overwrite the 2nd or 3rd month of a quarter, no problem, I
would use the End Method.

I'd select cell A2 and use the End Method
Range ("B2") .End(xlTo Right) .Select
And select the previous column.

I run into a problem when I want to overwrite the 1st month of a quarter's
entries as the End Method less 1 column to the left puts me in a quarterly
totals column.

How do I prevent overwriting any of the quarterly columns?

Thanks I advance.

Al

Conditional column selection
 

a range is specific to the sheet it is on, so you would have to change your
code slightly

rng.select
Dim Acol As Long
Acol = ActiveCell.Column ' unless you select rng, it isn't the activecell.

' do the required work and then

Sheets ("Sheet 2").Select
cells(1, Acol).Select
' or Columns(Acol).Select ' if you want the whole column
' do the required work

all that said, I would try to learn not to select and activate

set rng1 = Worksheets("Sheet 2").Cells(1,Acol)

rng1.value = 100

would work from sheet1 as an example. Or just do it directly

Worksheets("Sheet 2").Cells(1,Acol).Value = 100
--
Regards,
Tom Ogilvy


"AG" wrote in message
...
Very clean; thanks.
The solution exposed me to an entire new set of tools!

Can I then declare the resultant column as a variable AND call/reference
it to select other similar similarly setup sheets within the same workbook?

Such that:
set rng = Range("B2").End(xltoRight).offset(0,-1)
if rng.HasFormula then
set rng = rng.offset(0,1)
End if

Dim Acol As Range
Acol = ActiveCell.Column

' do the required work and then

Sheets ("Sheet 2").Select
Acol.Select
' do the required work


"Tom Ogilvy" wrote:

set rng = Range("B2").End(xltoRight).offset(0,-1)
if rng.HasFormula then
set rng = rng.offset(0,1)
End if

General approach - adjust to your specific situation.

--
Regards,
Tom Ogilvy


"AG" wrote in message
...
I have a monthly spreadsheet setup such that columns A & B are text,
column C is January, column D is February, column E is March, BUT column
F
are quarterly totals formulas and so on ending at column S which are
year to
date totals formulas. Entries begin in row 2.

On occasion I need to overwrite the monthly entries.


If I want to overwrite the 2nd or 3rd month of a quarter, no problem,
I
would use the End Method.

I'd select cell A2 and use the End Method
Range ("B2") .End(xlTo Right) .Select
And select the previous column.

I run into a problem when I want to overwrite the 1st month of a
quarter's
entries as the End Method less 1 column to the left puts me in a
quarterly
totals column.

How do I prevent overwriting any of the quarterly columns?

Thanks I advance.

Al