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.



0
Utf
1/14/2010 4:30:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
498 Views

Similar Articles

[PageSpeed] 9

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.
> 
> 
> 
0
Utf
1/14/2010 5:15:01 AM
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.
> 
> 
> 
0
Utf
1/14/2010 5:18:01 AM
Reply:

Similar Artilces:

Top values
Dear friends, need your help again please. I have a table: tbl_Plots (PlotID is the primar key - number byte) and tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots, height - number byte and diameter - number integer). I need to select the 20 bulkiest trees of each plot, i.e. having the biggest diameter. Also, perhaps in a plot less than 20 trees will be present so I will need all of them. Any suggestions? Thanking you in advance, GeorgeCY hi Geroge, George wrote: > I have a table: tbl_Plots (PlotID is the primar key - number byte) and > tbl_Data (ID is...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Convert double value 1.7E+20 to 1.722222222222222222222.
Hi All, I have double value 1.7E+20. I need to convert it and show it in textbox as below 1.722222222222222222222. how do we convert it? do we have any formats (string.format) in C#? Thanks in Advance VijayRama wrote: > Hi All, > > I have double value 1.7E+20. I need to convert it and show it in > textbox as below > > 1.722222222222222222222. 1.7E+20 isn't even close to 1.722222222222222222222, at least not by floating point standards. Why do you want the latter when given the former? > how do we convert it? do we have any formats (strin...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns (Item, Makebuy, Revision, IsActive) but is bound to the first column. However, I would like to pass the other values from the remaining columns to other fields in my form after the selection. Is this possible? Can someone provide a sample code? You can use the Column() attribute. Me.Itemcbx.Column(1) etc. Note that the index for combo box columns is zero-based so 0 is the first, 1 is the second and so on... Steve "Angel G" wrote: > In my form (Items_frm) I have a combo box (Itemcbx) that displ...

Deleting Unique Values
How do you delete unique values in a column? I need to filter 7500+ rows to only display duplicate values. The VBA code below will delete the entire row when the value in col. "A" will be uniqe. Consider to make a copy of your entire sheet in order to test the code and see if this is what you need. ------------------------------- Sub Delete_Uniques() LR = Cells(Rows.Count, 1).End(xlUp).Row For R = LR To 1 Step -1 If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then Cells(R, 1).EntireRow.Delete End If N...

Making words in a list a value!!
I am trying to create a list that will generate a value in another column once that word or phrase is chosen. Is this possible and how. Exp. In the drop down list I would chose Product, then in the price column the price automatically appears. brco1, Much better than I could ever explain the process. Here's exactly wha you need. http://www.contextures.com/xlFunctions02.html HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454 View this thread: http://www.excelforu...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Getting right date value
I setup my DTPicker control to be used only as a date control, yet I'm noticing that sometimes it will give back a date AND a time all in the same "value" variable. Since it appears that a variable of type "Date" can give back both a date and time, how can I eliminate the time half of a date value??? I might not be able to exactly control the DTPicker control to give me JUST a date, so I'm just curious what to do if it gives me back both a date & time. thank u Hi, Try this : Dim x as date x = cdate(clng(DTPicker1.value)) &qu...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find an answer searching the topics. I'm using the following formula in cell C3: =INDEX(LastName,MATCH(A3,EmpID,0)) When I type in an employee ID in A3, his/her last name shows in C3. However, when there is no value in A3, C3 shows error "#N/A". Is there a way to show a blank cell in C3 until a value is entered into A3? Thanks in advance! Mike On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...

#VALUE error when linking to other files
I have a summary sheet that pulls info from approx 10 other excel files. In one column of the summary sheet, the links perform just fine. Each cell in this column is the sum of an entire column in another workbook. When I open the summary page and choose "yes" to update with new information, everything works properly. However, in the same worksheet as this, I have another column where each cell is the result of a SUMIF where the data comes from another workbook. The odd part that I cannot understand is why when I open the summary sheet and update the information do all the cells...

Countif and values greater than zero for a named range
I have a workbook with 30+ worksheets. For each of the sheets, there is a cell (F51) that is part of a named range (PPG). On Sheet 1, cell c9, I want to sum the range (PPG) and then divide by ONLY the number of cells on the other sheets (F51) that are greater than 0. I am able to sum and then divide by count (=sum(PPG)/Count(PPG) but I cannot seem to get it to only count the cells that are greater than zero. When I attempt to use suggestions from other posts: (=sum(PPG)/COUNTIF(PPG)>0) I get error. Any help is appreciated. If you need Excel help on how the COUNT...

setting a column value in code for an updatable ViewGrid
Is there a way to set the value for a column in code when a ViewGrid row is edited? I have a row-last-updated date that is a required audit field in all our systems (required by data management). I don't really want the user updating this field as it should always be system controlled. If I create an editable ViewGrid tied to either an SqlDataSource or an ObjectDataSource, the field seems to only be updatable by the user. I can set it the first time by setting the DefaultValue in code but that has no effect after there is an initial value in the column. Do I need to co...

IF formula needs to read only values, not formulas
I have an IF formula down column F, and am trying to read the results with another IF formula in column G: =IF(F5 > 1/32, "Yes", "") But the formula in column G gives "Yes" when the cell in column F is filled only with the formula, not a result. How can I make this work? Ed Hi Ed, Please tell us what the exact formulas in F and G are; that is not clear from your post. What are the values involved? Are any of the columns formatted as Text before you entered the formulas? -- Kind regards, Niek Otten Microsoft MVP - Excel "Ed" <ed_millis@...

Pivot Table data values
I need make a pivot table using the values as the data. I used to be able to do this, but now it only sums, counts or calculates. Is there a way to return the data values? ...

Convert Pivot Table to Values in Macro
The code below converts multiple worksheets to values. Except, as I discovered recently Pivot Tables. Can you help modify the code to also convert Pivot Tables to Values? ============= Sub SetAllSheetsToValues() Dim shtSheet As Worksheet, shtActive As Worksheet Dim rngR As Range, rngCell As Range Application.ScreenUpdating = False Set shtActive = ActiveWorkbook.ActiveSheet For Each shtSheet In ActiveWorkbook.Sheets With shtSheet If .ProtectContents = False Then ' skip protected sheets On Error Resume Next Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _ xlErro...

Creating a chart with varying data value ranges
I have a chart that I want to show home price data with. The chart will have data that has very significantly varying data values. For example, one line graph will be 50-100, one will be 400,000 to 500,000, one will be 90% to 110%. I would like to show these line graphs on one chart. How to I make a chart with differing ranges on the x (vertical) axis? Take a bit of seutp work, but the end result here is impressive: http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html BTW, the vertical axis is the y-axis. -- Best Regards, Luke M "Anthony Blackburn&quo...

How do you change data values by dragging graph lines in excel 07?
Firstly, please ask your question in the body of the message, not the subject line. Secondly, you don't in Excel 2007; that was one of the "improvements" they put in. Most experts prefer the charting facilities in Excel 2003 over those in Excel 2007. -- David Biddulph "Mannymeister" <Mannymeister@discussions.microsoft.com> wrote in message news:3616E56A-A8BF-44D4-9E38-8746B39DCD7E@microsoft.com... > ...

Using a lookup to return multiple values in one cell??
Ok, basically I want to do a lookup to return some figures... I know all the VLOOKUP, etc... trouble is I don't just want to return one figure. It may be 1 figure but then it may be about 9/10 as well. I've got a list of order numbers and a list of orders that need an invoice date next to them. So I've done a bog standard vlookup and it only returns one figure where as there might be 10 invoices related to 1 order... make any sense :? (Table 1) Order Number................... Invoice Date 10010001....................... 22/11/05 10010001....................... 14/10/05 ...

Suppressing categories with 0 values
Hi all I'm trying to build a dynamic chart for sales data. The output file contains two columns, A and B. Column A contains categories, i.e. Apples, Oranges, Pears, etc.,. and B contains sales data, i.e. 5, 0, 10, etc.,. respectively. The ouput data comes out monthly. Some months will contain sales from all categories, some ten, some fifty. I want the chart to only graph categories with non-zero values, i.e. if there are 100 categories and only 30 with sales numbers there is no point in showing the other 70 categories with zero values. I can format column B with zero values to show ...