Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
how do I do subtractions in excel | New Users to Excel | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Compare lists for additions and subtractions | Excel Worksheet Functions | |||
Divide Column A by Column B multiply Column C | Excel Worksheet Functions |