VLOOKUP in VBA
On the worksheet I can insert in a cell
=VLOOKUP(C5, Hobokee.xls!AcsLow, 2)
and it works perfectly, looks up the value in column 2 of the range named
AcsLow in the same workbook.
But elswhere I want in a macro to lookup the same table and assign the
result to a variable BalAmt.
BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work.
It gives a function not defined error on Hobokee. If instead of Hobokee I
put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP
which it changes to VLookup.
I have been overVLOOKUP in the Help file and see nothing wrong.
What is wrong ?
Stuart
|
0 |
|
Stuart
3/20/2006 10:06:58 AM
excel
39879 articles. 
2 followers.
4 Replies
2393 Views
VLookup is a worksheet function so you need to specify it as such; WorksheetFunction.VLookup(etc...)
barryjonunattendedDim res as variant
dim lookupRng as range
dim lookupVal as range
set lookuprng = workbooks("hobokee.xls").range("acsLow")
set lookupval = workbooks("otherbook.xls").worksheets("sheet99").range("c5")
res = application.vlookup(lookupval,lookuprng,2)
if iserror(res) then
'same as #n/a
msgbox "not found"
else
msgbox res
end if
Stuart Grant wrote:
>
> On the worksheet I can insert in a cell
> =VLOOKUP(C5, Hobokee.xls!AcsLow, 2)
> and it works perfectly, looks up the value in column 2 of the range named
> AcsLow in the same workbook.
> But elswhere I want in a macro to lookup the same table and assign the
> result to a variable BalAmt.
> BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work.
> It gives a function not defined error on Hobokee. If instead of Hobokee I
> put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP
> which it changes to VLookup.
> I have been overVLOOKUP in the Help file and see nothing wrong.
> What is wrong ?
> Stuart
--
Dave Peterson
petersodBarry-Jon Thanks for your prompt help. Pity that when you look up VLookup in VBA help, there is no mention of WorkshopFunction. I had a little trouble with the range too but have got this sorted out. Stuart "Barry-Jon" <barryjonunattended@yahoo.co.uk> wrote in message news:1142853196.233182.97280@i40g2000cwc.googlegroups.com... > VLookup is a worksheet function so you need to specify it as such; > > WorksheetFunction.VLookup(etc...) >
|
|
0 |
|
Stuart
3/20/2006 3:02:30 PM
Look for worksheetfunction in VBA's help. But for any function, you'll find the Excel's help (not VBA's) is where you'll want to check. Stuart Grant wrote: > > Barry-Jon > > Thanks for your prompt help. Pity that when you look up VLookup in VBA > help, there is no mention of WorkshopFunction. > I had a little trouble with the range too but have got this sorted out. > > Stuart > > "Barry-Jon" <barryjonunattended@yahoo.co.uk> wrote in message > news:1142853196.233182.97280@i40g2000cwc.googlegroups.com... > > VLookup is a worksheet function so you need to specify it as such; > > > > WorksheetFunction.VLookup(etc...) > > -- Dave Peterson
Similar Artilces:
using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...
VBA code to capture Computer device unique ID number
I am seeking knowledge of how to write a VBA code in access 2003 that allows me to call a property function by which I can capture a unique number which identifies the computer hardware device. I am thinking of something like the MAK Number that is associated with the hardware of interest since it is unique and cannot be repeated in any other device. The purpose of this function is that I would grant accessibility for users to my database application only if the hardware can be identified in a table that contains approved MAK numbers. I would greatly appreciate the help I can get in thi...
in vba for access how do i get the day of the week for a date field?
in vba for access how do i get the day of the week for a date field? You mean Monday, Tuesday,... Dim myDate As Date myDate = DateSerial(2005, 12, 25) MsgBox Format(myDate, "dddd") or the day portion of the date? Dim myDate As Date myDate = DateSerial(2005, 12, 25) MsgBox Day(myDate) Daniel wrote: > > in vba for access how do i get the day of the week for a date field? -- Dave Peterson ...
vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...
Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...
Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...
VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...
vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...
using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...
VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...
VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...
combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...
VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...
Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...
incorporate paste value in vlookup
is there a way to incorporate "paste value" in a vlookup formula ? I have the vlookups in the column working and feeding off of the multiple worksheets. the data will be updated weekly (and new columns with the updated data added) and i would rather just copy new data on top of the old in the worksheets instean of having to add new worksheets with new data... as to keep the previous column from changing. -- problem ...
vlookup inside an if statement?
-------------------------------------------------------------------------------- Hi everybody! I have to do a vertical lookup of an object that might or might not exist, in case it doesnt exist normally it gives back Ref! but the thing is that if this objet doesnt appear on the data base is because is zero. This Ref! is not allowing me to make a sum since this objet is part of it. Maybe with an example would be easier to explain. i have to pull from a database the number of cars sold of three brands A,B, C, In the database unless is one car of the type it will not appear. lets say we hav...
VBA in Excel 2007, NumberFormat
Data entered into a form (in ADD_REP_DATE and ADD_PRD) are in "mm/dd/yyyy" format, and I'm trying to put them in cells with "mmm-yy" format, but these statements aren't doing it. I've tried moving the two format statements before the data entry statements, to no effect. NEC_SELECT.Offset(I, 0).Cells(1, 16) = Me.ADD_REP_DATE NEC_SELECT.Offset(I, 0).Cells(1, 17) = Me.ADD_PRD NEC_SELECT.Offset(I, 0).Cells(1, 16).NumberFormat = "mmm-yy" NEC_SELECT.Offset(I, 0).Cells(1, 17).NumberFormat = "mmm-yy&...
Nested vlookup
My formula is: =((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001 The formula looks up the value in "$W..."and returns the amount of orders. I alwayshave values for the first part of the lookup, but niot the second. THe issue is that if there nothing to report back for the second lookup, it gives me an "#NA" errror instead of at least returning the value for the first lookup. Any help? Thanks, TJ Wrap both Lookups in their own ISNA() wrapper to trap for that error:- =(IF(ISNA(VLookup1),0,VLookup1)+IF(ISNA(VL...
Vlookup?
I am trying to sum the cells in column "AY" for each row that has a positive value in column "S" I am very confused and this is what I have so far. Can you help me please? =SUM(VLOOKUP($S$3:$S$502>0,$S3:$AY502,33,FALSE)) Try =SUMIF(S3:S502,">0",AY3:AY502) Hope this helps, Hutch "Doug" wrote: > I am trying to sum the cells in column "AY" for each row that has a positive > value in column "S" > I am very confused and this is what I have so far. Can you help me please? > > =SUM(VLOOKUP($S...
Why #NA when using VLOOKUP?
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C7C7AD.F7E2E420 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I'm trying to use VLOOKUP to find lowest value in a small group. The = exact sample is below: A B 1 3.0001 A=20 2 2.9442 B=20 3 2.9610 C=20 4 2.9055 D=20 5 2.9630 E=20 The formula I'm using is =3DVLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to = get it to return the lowest cost, from column A. If I take out Row 4, = it wo...
VBA Question
Hi, I have a form with a combobox control that list some sizes. They are: 12 18 20 22 I have another text box for keying a custom size, such as 12'2". What I would like to do is have the combobox default to the next size (in this case 18). Here's the code I have used so far (it does a little more than what I am asking for but to give an idea): Dim MCUST As String MCUST = Me.custwidth If Str(Me.widthck) <> Me.custwidth Or Len(Me.custwidth) <> Len(Me.widthck) Then Me.widthck.Value = Int(Val(MCUST)) + 1 If InStr(Int(Me.widthck) / 2, ".&...
VLookup in other document
Here is my problem. Please help I have two documents A and B. In document A I have a table existing of codes and descriptions Range A1: B100 Column A contains codes, Column B contains descriptions In document B I want to lookup the description for a given code. I have a cell C10 containing the code and a cell C11 containing a formula =VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2) Till so far it works !! But I use this formula many times in my excel sheet and in many Excel documents, so if Ithe lokation of document A changes, I have to change all the for...
formula to VBA
How do I go from this formula =INT(G20/12) & " ft. " & MOD(G20,12) & " in." to a line in VBA that will perform in this function: Function ConvertInches(intInches) Dim intInches As Integer Dim intFeet As Integer ConvertInches = INT(intInches/12) & " ft. " & MOD(intInches,12) & " in." End Function I know I'm screwing it up but hopefully it's a simple fix. Thanks, hi, try this. The correct syntax for MOD in VB is 'Num MOD divisor' but unfortunately we cant use that...
VBA If condition
I want to test for something like this - If Cells (oRow.Row, "AT").Value = "1" OR "2" OR "3" Then Cells(oRow.Row, "BA").Value = 0 ElseIf.... buy am unclear how to express the OR conditions in VBA. All help gratefully received and acknowledged! TIA, Dan -- Dan E webbie(removethis)@preferredcountry.com Try something like If Cells(oRow.Row,"AT").Value = "1" Or _ Cells(oRow.Row,"AT").Value = "2" Or _ Cells(oRow.Row,"AT").Value= "3" Then Cells(oRow.Row,"BA&qu...
Checking for name in VBA with wildcard?
I've got a column where occasionaly appears text of the following form: Zone 1, Zone 2 etc. I want to clear the cell to the right of any cell that contains this text; however, I can't figure out how to write the code so that it will look for Zone 1, then Zone 2 etc. Here's what I've got so far, try not to laugh, I'm a beginner :) Sub ClearEmpty() Dim rMyCell As Range Dim i As Integer For i = 1 To 60 For Each rMyCell In Range("A1:A60") If rMyCell.Value = "Zone" & i Then rMyCell.Select ActiveCell.Offset(0, 1).Value = "" ...
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...
VBA code to capture Computer device unique ID number
I am seeking knowledge of how to write a VBA code in access 2003 that allows me to call a property function by which I can capture a unique number which identifies the computer hardware device. I am thinking of something like the MAK Number that is associated with the hardware of interest since it is unique and cannot be repeated in any other device. The purpose of this function is that I would grant accessibility for users to my database application only if the hardware can be identified in a table that contains approved MAK numbers. I would greatly appreciate the help I can get in thi...
in vba for access how do i get the day of the week for a date field?
in vba for access how do i get the day of the week for a date field? You mean Monday, Tuesday,... Dim myDate As Date myDate = DateSerial(2005, 12, 25) MsgBox Format(myDate, "dddd") or the day portion of the date? Dim myDate As Date myDate = DateSerial(2005, 12, 25) MsgBox Day(myDate) Daniel wrote: > > in vba for access how do i get the day of the week for a date field? -- Dave Peterson ...
vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...
Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...
Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...
VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...
vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...
using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...
VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...
VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...
combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...
VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...
Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...
incorporate paste value in vlookup
is there a way to incorporate "paste value" in a vlookup formula ? I have the vlookups in the column working and feeding off of the multiple worksheets. the data will be updated weekly (and new columns with the updated data added) and i would rather just copy new data on top of the old in the worksheets instean of having to add new worksheets with new data... as to keep the previous column from changing. -- problem ...
vlookup inside an if statement?
-------------------------------------------------------------------------------- Hi everybody! I have to do a vertical lookup of an object that might or might not exist, in case it doesnt exist normally it gives back Ref! but the thing is that if this objet doesnt appear on the data base is because is zero. This Ref! is not allowing me to make a sum since this objet is part of it. Maybe with an example would be easier to explain. i have to pull from a database the number of cars sold of three brands A,B, C, In the database unless is one car of the type it will not appear. lets say we hav...
VBA in Excel 2007, NumberFormat
Data entered into a form (in ADD_REP_DATE and ADD_PRD) are in "mm/dd/yyyy" format, and I'm trying to put them in cells with "mmm-yy" format, but these statements aren't doing it. I've tried moving the two format statements before the data entry statements, to no effect. NEC_SELECT.Offset(I, 0).Cells(1, 16) = Me.ADD_REP_DATE NEC_SELECT.Offset(I, 0).Cells(1, 17) = Me.ADD_PRD NEC_SELECT.Offset(I, 0).Cells(1, 16).NumberFormat = "mmm-yy" NEC_SELECT.Offset(I, 0).Cells(1, 17).NumberFormat = "mmm-yy&...
Nested vlookup
My formula is: =((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001 The formula looks up the value in "$W..."and returns the amount of orders. I alwayshave values for the first part of the lookup, but niot the second. THe issue is that if there nothing to report back for the second lookup, it gives me an "#NA" errror instead of at least returning the value for the first lookup. Any help? Thanks, TJ Wrap both Lookups in their own ISNA() wrapper to trap for that error:- =(IF(ISNA(VLookup1),0,VLookup1)+IF(ISNA(VL...
Vlookup?
I am trying to sum the cells in column "AY" for each row that has a positive value in column "S" I am very confused and this is what I have so far. Can you help me please? =SUM(VLOOKUP($S$3:$S$502>0,$S3:$AY502,33,FALSE)) Try =SUMIF(S3:S502,">0",AY3:AY502) Hope this helps, Hutch "Doug" wrote: > I am trying to sum the cells in column "AY" for each row that has a positive > value in column "S" > I am very confused and this is what I have so far. Can you help me please? > > =SUM(VLOOKUP($S...
Why #NA when using VLOOKUP?
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C7C7AD.F7E2E420 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I'm trying to use VLOOKUP to find lowest value in a small group. The = exact sample is below: A B 1 3.0001 A=20 2 2.9442 B=20 3 2.9610 C=20 4 2.9055 D=20 5 2.9630 E=20 The formula I'm using is =3DVLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to = get it to return the lowest cost, from column A. If I take out Row 4, = it wo...
VBA Question
Hi, I have a form with a combobox control that list some sizes. They are: 12 18 20 22 I have another text box for keying a custom size, such as 12'2". What I would like to do is have the combobox default to the next size (in this case 18). Here's the code I have used so far (it does a little more than what I am asking for but to give an idea): Dim MCUST As String MCUST = Me.custwidth If Str(Me.widthck) <> Me.custwidth Or Len(Me.custwidth) <> Len(Me.widthck) Then Me.widthck.Value = Int(Val(MCUST)) + 1 If InStr(Int(Me.widthck) / 2, ".&...
VLookup in other document
Here is my problem. Please help I have two documents A and B. In document A I have a table existing of codes and descriptions Range A1: B100 Column A contains codes, Column B contains descriptions In document B I want to lookup the description for a given code. I have a cell C10 containing the code and a cell C11 containing a formula =VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2) Till so far it works !! But I use this formula many times in my excel sheet and in many Excel documents, so if Ithe lokation of document A changes, I have to change all the for...
formula to VBA
How do I go from this formula =INT(G20/12) & " ft. " & MOD(G20,12) & " in." to a line in VBA that will perform in this function: Function ConvertInches(intInches) Dim intInches As Integer Dim intFeet As Integer ConvertInches = INT(intInches/12) & " ft. " & MOD(intInches,12) & " in." End Function I know I'm screwing it up but hopefully it's a simple fix. Thanks, hi, try this. The correct syntax for MOD in VB is 'Num MOD divisor' but unfortunately we cant use that...
VBA If condition
I want to test for something like this - If Cells (oRow.Row, "AT").Value = "1" OR "2" OR "3" Then Cells(oRow.Row, "BA").Value = 0 ElseIf.... buy am unclear how to express the OR conditions in VBA. All help gratefully received and acknowledged! TIA, Dan -- Dan E webbie(removethis)@preferredcountry.com Try something like If Cells(oRow.Row,"AT").Value = "1" Or _ Cells(oRow.Row,"AT").Value = "2" Or _ Cells(oRow.Row,"AT").Value= "3" Then Cells(oRow.Row,"BA&qu...
Checking for name in VBA with wildcard?
I've got a column where occasionaly appears text of the following form: Zone 1, Zone 2 etc. I want to clear the cell to the right of any cell that contains this text; however, I can't figure out how to write the code so that it will look for Zone 1, then Zone 2 etc. Here's what I've got so far, try not to laugh, I'm a beginner :) Sub ClearEmpty() Dim rMyCell As Range Dim i As Integer For i = 1 To 60 For Each rMyCell In Range("A1:A60") If rMyCell.Value = "Zone" & i Then rMyCell.Select ActiveCell.Offset(0, 1).Value = "" ...