Column subtractions
 

I have an input box that asks the user to input a new column for a range to
set the print area. I have on sheet that has one less column than the rest.
Is there a way to have it take off on column?

Here is what I have

newcolumn = InputBox("New Column", "New Column for Trends")

User puts in AJ

On one sheet I only want it to go to AI is there a way to do this without
asking another question?

Column subtractions
 

On Oct 4, 9:30 am, Mike Darrington
wrote:
I have an input box that asks the user to input a new column for a range to
set the print area. I have on sheet that has one less column than the rest.
Is there a way to have it take off on column?

Here is what I have

newcolumn = InputBox("New Column", "New Column for Trends")

User puts in AJ

On one sheet I only want it to go to AI is there a way to do this without
asking another question?

Hi Mike,

I guess the simplest solution is to insert a column in the worksheet
so it has the same number of columns as all the other sheets and then
hide the new column. But if that's not an option...

You can set up an array containing the column indices (letters) either
in your code or in a worksheet somewhere (probably hidden) so that you
can substitute AI for AJ when required.

eg if array is in a worksheet simply have a one column array that
starts with A in the first cell then B in next cell down then C then D
etc. Name your array and reference it in your code when setting the
print area for the unusual sheet. Assuming you've named it
"columnArray" and it's in the first worksheet your code could look
something like this:

With Worksheets(1).Range("columnArray")
Set c = .Find(newcolumn, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
foundAddress = c.Address
prevCol = Range(foundAddress).Offset(-1, 0).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

This will search through the array until it finds AJ (or whatever the
value of newcolumn is) then assign the value of the cell above (which
has the previous column index in it) to prevCol. Use prevCol when
setting the print area for the unusual sheet.

Hope this makes sense and helps!

cheers,
t.

Column subtractions
 

Option Explicit
Sub testme()

Dim NewColumn As String
Dim NewColNum As Long
Dim TestRng As Range

NewColumn = InputBox("New Column", "New Column for Trends")

Set TestRng = Nothing
On Error Resume Next
Set TestRng = Worksheets(1).Cells(1, NewColumn)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "That's not a valid column"
Exit Sub
End If

NewColNum = TestRng.Column

'then you could use NewColNum-1 to be the column to the right.

With ActiveSheet
MsgBox .Range("A1", .Cells(99, NewColNum - 1)).Address(0, 0)
End With

End Sub

If you use .cells(x,y), the y portion represents the column. And excel's VBA
will allow you to use either a string or a number as that argument.

Mike Darrington wrote:

I have an input box that asks the user to input a new column for a range to
set the print area. I have on sheet that has one less column than the rest.
Is there a way to have it take off on column?

Here is what I have

newcolumn = InputBox("New Column", "New Column for Trends")

User puts in AJ

On one sheet I only want it to go to AI is there a way to do this without
asking another question?

--

Dave Peterson