Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling cell "value"
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling cell "value"
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling cell "value"
thanks that works great
"Chad Portman" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Pulling "Irregular Shaped" Data from Excel | Excel Programming | |||
Pulling rows from tabs to a "summary" sheet | Excel Worksheet Functions | |||
Help! Boss needs this ASAP - I am pulling out my hair here folks use of "&" in HEADER | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |