C
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.
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.