Storing column() to use later
 

In a spreadsheet, I have the years 2007 to 2050 along the top of a table and
I have the months of the year down the left most column (with the week number
below each month). I want to find a year along the top, identify the column
number associated to that year, and store that column number to use later in
the macro. What would be the code?

Thanks.

Storing column() to use later
 

dim res as variant
dim myYear as long
dim myRng as range

with worksheets("sheet9999")
set myrng = .rows(1) 'first row
end with

myyear = 2013 'say
res = application.match(myyear,myrng,0)

if iserror(res) then
msgbox myYear & " wasn't found!
else
'the column number is res
'do the real work
end if



Marco wrote:

In a spreadsheet, I have the years 2007 to 2050 along the top of a table and
I have the months of the year down the left most column (with the week number
below each month). I want to find a year along the top, identify the column
number associated to that year, and store that column number to use later in
the macro. What would be the code?

Thanks.

--

Dave Peterson

Storing column() to use later
 

If 2007 is the start year, subtract that from your target row, then add the
column number for year 2007, that's your column number.

For example, if 2007 is in column C, to find year 2013

findyear = 2013
startyear = 2007
startcol = 2
myCol = findyear - startyear + startcol

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Marco" wrote in message
...
In a spreadsheet, I have the years 2007 to 2050 along the top of a table
and
I have the months of the year down the left most column (with the week
number
below each month). I want to find a year along the top, identify the
column
number associated to that year, and store that column number to use later
in
the macro. What would be the code?

Thanks.

Storing column() to use later
 

Let me elaborate, what I want to do is in a message box, ask for the year.
The macro will CTL+F along the top of the row to that year. That's the
column I want stored. Then I will have the macro look for certain months and
certain weeks (pre defined for a report), offset by the number stored, and
will extract the amount for that "year and month/week".

Will the code you wrote work for this task?

Thanks.

"Dave Peterson" wrote:

dim res as variant
dim myYear as long
dim myRng as range

with worksheets("sheet9999")
set myrng = .rows(1) 'first row
end with

myyear = 2013 'say
res = application.match(myyear,myrng,0)

if iserror(res) then
msgbox myYear & " wasn't found!
else
'the column number is res
'do the real work
end if



Marco wrote:

In a spreadsheet, I have the years 2007 to 2050 along the top of a table and
I have the months of the year down the left most column (with the week number
below each month). I want to find a year along the top, identify the column
number associated to that year, and store that column number to use later in
the macro. What would be the code?

Thanks.

--

Dave Peterson

Storing column() to use later
 

Thanks but this didn't help.

I actually want to do several searches. One along the row for a year (the
macro will ask for it and do a find ). I want that =column() stored. Then
from a predifined report, go to certain months and weeks (a find, again) and
extract the information for that year only (offset code with the stored value
along that row only, if possible).

Any other suggestions?

"Bob Phillips" wrote:

If 2007 is the start year, subtract that from your target row, then add the
column number for year 2007, that's your column number.

For example, if 2007 is in column C, to find year 2013

findyear = 2013
startyear = 2007
startcol = 2
myCol = findyear - startyear + startcol

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Marco" wrote in message
...
In a spreadsheet, I have the years 2007 to 2050 along the top of a table
and
I have the months of the year down the left most column (with the week
number
below each month). I want to find a year along the top, identify the
column
number associated to that year, and store that column number to use later
in
the macro. What would be the code?

Thanks.

Storing column() to use later
 

Almost:

Option Explicit
Sub testme()
Dim res As Variant
Dim myYear As Long
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Rows(1) 'first row
End With

myYear = Application.InputBox("Please enter a year", Type:=1)
myYear = Int(myYear)

'just a little checking
If myYear < 2000 _
Or myYear 2025 Then
'quit
Exit Sub
End If

res = Application.Match(myYear, myRng, 0)

If IsError(res) Then
MsgBox myYear & " wasn't found!"
Else
'the column number is res
'do the real work
MsgBox "I found year: " & myYear & " in column " & res
End If

End Sub


Marco wrote:

Let me elaborate, what I want to do is in a message box, ask for the year.
The macro will CTL+F along the top of the row to that year. That's the
column I want stored. Then I will have the macro look for certain months and
certain weeks (pre defined for a report), offset by the number stored, and
will extract the amount for that "year and month/week".

Will the code you wrote work for this task?

Thanks.

"Dave Peterson" wrote:

dim res as variant
dim myYear as long
dim myRng as range

with worksheets("sheet9999")
set myrng = .rows(1) 'first row
end with

myyear = 2013 'say
res = application.match(myyear,myrng,0)

if iserror(res) then
msgbox myYear & " wasn't found!
else
'the column number is res
'do the real work
end if



Marco wrote:

In a spreadsheet, I have the years 2007 to 2050 along the top of a table and
I have the months of the year down the left most column (with the week number
below each month). I want to find a year along the top, identify the column
number associated to that year, and store that column number to use later in
the macro. What would be the code?

Thanks.

--

Dave Peterson


--

Dave Peterson

Storing column() to use later
 

I gave a suggestion to get the column. You can use that to index directly
in.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Marco" wrote in message
...
Thanks but this didn't help.

I actually want to do several searches. One along the row for a year (the
macro will ask for it and do a find ). I want that =column() stored.
Then
from a predifined report, go to certain months and weeks (a find, again)
and
extract the information for that year only (offset code with the stored
value
along that row only, if possible).

Any other suggestions?

"Bob Phillips" wrote:

If 2007 is the start year, subtract that from your target row, then add
the
column number for year 2007, that's your column number.

For example, if 2007 is in column C, to find year 2013

findyear = 2013
startyear = 2007
startcol = 2
myCol = findyear - startyear + startcol

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Marco" wrote in message
...
In a spreadsheet, I have the years 2007 to 2050 along the top of a
table
and
I have the months of the year down the left most column (with the week
number
below each month). I want to find a year along the top, identify the
column
number associated to that year, and store that column number to use
later
in
the macro. What would be the code?

Thanks.

Storing column() to use later
 

Thanks.

"Dave Peterson" wrote:

Almost:

Option Explicit
Sub testme()
Dim res As Variant
Dim myYear As Long
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Rows(1) 'first row
End With

myYear = Application.InputBox("Please enter a year", Type:=1)
myYear = Int(myYear)

'just a little checking
If myYear < 2000 _
Or myYear 2025 Then
'quit
Exit Sub
End If

res = Application.Match(myYear, myRng, 0)

If IsError(res) Then
MsgBox myYear & " wasn't found!"
Else
'the column number is res
'do the real work
MsgBox "I found year: " & myYear & " in column " & res
End If

End Sub


Marco wrote:

Let me elaborate, what I want to do is in a message box, ask for the year.
The macro will CTL+F along the top of the row to that year. That's the
column I want stored. Then I will have the macro look for certain months and
certain weeks (pre defined for a report), offset by the number stored, and
will extract the amount for that "year and month/week".

Will the code you wrote work for this task?

Thanks.

"Dave Peterson" wrote:

dim res as variant
dim myYear as long
dim myRng as range

with worksheets("sheet9999")
set myrng = .rows(1) 'first row
end with

myyear = 2013 'say
res = application.match(myyear,myrng,0)

if iserror(res) then
msgbox myYear & " wasn't found!
else
'the column number is res
'do the real work
end if



Marco wrote:

In a spreadsheet, I have the years 2007 to 2050 along the top of a table and
I have the months of the year down the left most column (with the week number
below each month). I want to find a year along the top, identify the column
number associated to that year, and store that column number to use later in
the macro. What would be the code?

Thanks.

--

Dave Peterson


--

Dave Peterson