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
|