pulling cell "value"

[Chad Portman]

in the cell C4 i have the formula =now() and I have that cell formatted to
custom mmmm so that it displays as January.

I am now trying to write some VBA code to look at cell C4 and take the
January and then do a vlookup on the array that i have named months.

months is the cells D14:E25 column D has a list of the months and column E
has a list of numbers that corresponds to these months. This is a custom list
so I can not use the standard numbers that excel uses for the months.

The vlookup in vba should take the January and find the number in col E that
corresponds to it then take that number and use it as a auto filter criteria
for antoher sheet.

Below is the code I am using in Excel 2003 and it gives me a overflow error
Sub NIBRJan()

Dim i As Integer
Dim found As Variant

Sheets("Breakdown").Select
Range("C4").Select
i = ActiveCell.Value
found = Application.VLookup(i, months, 2, 0)
i = found
Sheets("Datadrop").Select
Selection.AutoFilter field:=3, Criteria1:=i
Selection.AutoFilter field:=7, Criteria1:="=Below Requirements",
Operator _
:=xlOr, Criteria2:="=Needs Improvement"
Range("B449").Select
Selection.Copy
Sheets("Breakdown").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Datadrop").Select
Selection.AutoFilter field:=7
Selection.AutoFilter field:=3
Sheets("Breakdown").Select

End Sub

If i change the dim i as integer and make it just dim i it seems to work
expect for that it pulls the full value of =now() instead of just january and
therfore will not do the lookup right.

Sorry for any misspelling I am not good at that and any help is appreciated.

 

[FSt1]

hi
instead of i = activecell.value
try
i = month(activecell.value)

in fact you don't need to select C4.
instead of
Range("C4").Select
i = ActiveCell.Value

try
i = month(range("C4").value)

seldom do you need to accually do any selecting. by not selecting, you may
even speed up your code and cut down some of your typing.
but then you like to see the screen jumping all over the
place......considered by some as a pseudo progress meter or user
entertainment.

your code....your call.

regards
FSt1

 

[Chad Portman]

thanks that works great