macros and column selection
 

Hello, I am creating a macro and need it to automatically select certain
columns. the problem I am having is that every two weeks the number of
columns that need to be selected will increase by 1. I need it to select up
to the cell that has the words "Fiscal Date - All". Is this possible?

thanks, janet

macros and column selection
 

Janet,

Try some code like the following. This assumes that "Fiscal
Date - All" is in row 1.


Dim Rng As Range
Set Rng = Rows(1).Find("fiscal date - all")
If Not Rng Is Nothing Then
Range(Range("a1"), Rng).EntireColumn.Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JanetH" wrote in message
...
Hello, I am creating a macro and need it to automatically
select certain
columns. the problem I am having is that every two weeks the
number of
columns that need to be selected will increase by 1. I need it
to select up
to the cell that has the words "Fiscal Date - All". Is this
possible?

thanks, janet

macros and column selection
 

thanks so much Chip.

Sorry I should have explained myself better. I have the macro search for the
word Sick and then go down one row (to where the data is) and then I want it
to go across to the column just before the one that has the totals.

it looks like
Sick
05/06 01 05/06 02 .... Fiscal Date All
7.5 2.1 15.2

is that possible?

thanks, Janet

"Chip Pearson" wrote:

Janet,

Try some code like the following. This assumes that "Fiscal
Date - All" is in row 1.


Dim Rng As Range
Set Rng = Rows(1).Find("fiscal date - all")
If Not Rng Is Nothing Then
Range(Range("a1"), Rng).EntireColumn.Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JanetH" wrote in message
...
Hello, I am creating a macro and need it to automatically
select certain
columns. the problem I am having is that every two weeks the
number of
columns that need to be selected will increase by 1. I need it
to select up
to the cell that has the words "Fiscal Date - All". Is this
possible?

thanks, janet

macros and column selection
 

Watch your headers. You've spelled "fiscal data all" a couple of different
ways:

Option Explicit
Sub testme01()

Dim RngFiscal As Range
Dim RngSick As Range

With Worksheets("sheet1")
.Activate

With .Rows(1)
Set RngFiscal = .Find(what:="Fiscal Date All", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If RngFiscal Is Nothing Then
MsgBox "Error in headers"
Exit Sub
End If

With .Range("a:a")
Set RngSick = .Find(what:="sick", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If RngSick Is Nothing Then
MsgBox "Sick Not found"
Exit Sub
End If

'come down one row
Set RngSick = RngSick.Offset(1, 0)

.Range(RngSick, .Cells(RngSick.Row, RngFiscal.Column - 1)).Select

End With

End Sub

And this assumes that your data is in row 1 and column A--on Sheet1.

JanetH wrote:

thanks so much Chip.

Sorry I should have explained myself better. I have the macro search for the
word Sick and then go down one row (to where the data is) and then I want it
to go across to the column just before the one that has the totals.

it looks like
Sick
05/06 01 05/06 02 .... Fiscal Date All
7.5 2.1 15.2

is that possible?

thanks, Janet

"Chip Pearson" wrote:

Janet,

Try some code like the following. This assumes that "Fiscal
Date - All" is in row 1.


Dim Rng As Range
Set Rng = Rows(1).Find("fiscal date - all")
If Not Rng Is Nothing Then
Range(Range("a1"), Rng).EntireColumn.Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JanetH" wrote in message
...
Hello, I am creating a macro and need it to automatically
select certain
columns. the problem I am having is that every two weeks the
number of
columns that need to be selected will increase by 1. I need it
to select up
to the cell that has the words "Fiscal Date - All". Is this
possible?

thanks, janet




--

Dave Peterson