if then statement with a vlookup?

Hello,
   I am trying to come up some code to search a workbook for numbers and 
then put them in a column on the last page but if the number already exists, 
do nothing. I need it to do this everytime I run the macro. Any ideas?

here is an example:

look for "day" if true then i want the number in column j on that page to go 
to the last page in column k, but if the number is already there then do 
nothing. if it is not there then the next cell down.

Does this make any sense? I think I confused myself.

Any help is appreciated

Thank you
0
Utf
11/24/2009 4:16:01 PM
excel.programming 6508 articles. 2 followers. Follow

8 Replies
517 Views

Similar Articles

[PageSpeed] 58

A little clarification please...

1. When you say "search the workbook", do you mean search on every worksheet 
in the workbook except for the last worksheet? Or did you actually mean to 
search on a single worksheet? If only a single worksheet, what is that 
worksheet's name?

2. What range on the worksheet(s) is to be searched? A single column or row? 
Multiple columns or rows? Which column(s) or row(s) are they?

3. The **only** thing you want placed in Column K on the last worksheet is a 
number? Nothing else related to the found text is going to be put on that 
worksheet... just a column of numbers???

4. What is the worksheet name of your "last page"?

-- 
Rick (MVP - Excel)


"AwesomeSean" <AwesomeSean@discussions.microsoft.com> wrote in message 
news:02F4DFA8-8EB2-4BE4-98DA-E2B5FB17BC05@microsoft.com...
> Hello,
>   I am trying to come up some code to search a workbook for numbers and
> then put them in a column on the last page but if the number already 
> exists,
> do nothing. I need it to do this everytime I run the macro. Any ideas?
>
> here is an example:
>
> look for "day" if true then i want the number in column j on that page to 
> go
> to the last page in column k, but if the number is already there then do
> nothing. if it is not there then the next cell down.
>
> Does this make any sense? I think I confused myself.
>
> Any help is appreciated
>
> Thank you 

0
Rick
11/24/2009 4:29:37 PM
Thank you for the reply. Here is a little better explanation (I think)

I have a workbook with 7 pages I want to search for "Rescheduled" in column 
D (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Other) If 
"Rescheduled" appears in column D, Then look at column C to see if it says 
"scheduled or unscheduled" if all this is true then I want the work order 
number in column G on that same line to go to 'WTD CHART DATA' page in a box 
I made where the upper left corner starts on K140 to L165 (2 column box)

There are 2 columns in the box. K141 says scheduled and L141 says unscheduled.
I want the work order number (from row G on the other pages) to go in the 
appropiate column.

Here is the other part. 
If there are already work order numbers in there I don't want to keep 
duplicating them so if the number is already there then do nothing. If the 
work order is not there then go to the next empty cell down.

Did that help or did I make it worse?

Thank you, Thank you, Thank you
0
Utf
11/24/2009 5:43:01 PM
Try the following macro. I wasn't entirely clear on which sheets needed to 
be processed (you said there were 7 sheets and then listed 8 names), so you 
should check the names I used inside the Array function call on the fourth 
line of active code where I assign the output from the Array function call 
to the SearchSheet variable. Also, I wasn't sure where your "scheduled" and 
"unscheduled" headers were on the 'WTD CHART DATA' sheet, so I assumed they 
were in K141 and L141 with the data to be listed under them. There are 
ranges that would have to be adjusted if that guess was wrong. Let me know 
if you need help with that part. Also, the "scheduled" and "unscheduled" 
headers are needed as my code needs to find their text when figuring out 
where to put the "order number". Anyway, give this macro a try and let me 
know how it works out...

Sub ProcessReschedules()
  Dim X As Long, C As Range, FirstAddress As String, SearchSheet As Variant
  Const SearchWord As String = "Rescheduled"
  Const Destination As String = "WTD CHART DATA"
  SearchSheet = Array("Monday", "Tuesday", "Wednesday", "Thursday", _
                       "Friday", "Saturday", "Sunday", "Other")
  On Error Resume Next
  For X = LBound(SearchSheet) To UBound(SearchSheet)
    With Worksheets(SearchSheet(X))
      Set C = .Range("D:D").Find(SearchWord, After:=.Cells( _
                        Rows.Count, "D"), LookIn:=xlValues)
      If Not C Is Nothing Then
        FirstAddress = C.Address
        Do
          If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
                        C.Offset(0, -1).Value) = "scheduled" Then
            Worksheets(Destination).Cells(Worksheets(Destination). _
               Range("K141:K" & Rows.Count).Find(What:="*", _
               SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
               LookIn:=xlValues).Row + 1, "K").Value = C.Offset(0, 3).Value
          ElseIf LCase(C.Offset(0, -1).Value) = "unscheduled" Then
            Worksheets(Destination).Cells(Worksheets(Destination). _
               Range("L141:L" & Rows.Count).Find(What:="*", _
               SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
               LookIn:=xlValues).Row + 1, "L").Value = C.Offset(0, 3).Value
          End If
          Set C = .Range("D:D").Find(SearchWord, After:=C, LookIn:=xlValues)
        Loop While Not C Is Nothing And C.Address <> FirstAddress
      End If
    End With
  Next
End Sub

-- 
Rick (MVP - Excel)


"AwesomeSean" <AwesomeSean@discussions.microsoft.com> wrote in message 
news:8D2A222B-7B6C-4137-9613-B02E7EC29FF4@microsoft.com...
> Thank you for the reply. Here is a little better explanation (I think)
>
> I have a workbook with 7 pages I want to search for "Rescheduled" in 
> column
> D (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Other) 
> If
> "Rescheduled" appears in column D, Then look at column C to see if it says
> "scheduled or unscheduled" if all this is true then I want the work order
> number in column G on that same line to go to 'WTD CHART DATA' page in a 
> box
> I made where the upper left corner starts on K140 to L165 (2 column box)
>
> There are 2 columns in the box. K141 says scheduled and L141 says 
> unscheduled.
> I want the work order number (from row G on the other pages) to go in the
> appropiate column.
>
> Here is the other part.
> If there are already work order numbers in there I don't want to keep
> duplicating them so if the number is already there then do nothing. If the
> work order is not there then go to the next empty cell down.
>
> Did that help or did I make it worse?
>
> Thank you, Thank you, Thank you 

0
Rick
11/24/2009 10:14:40 PM
Macro does not run at all. I made a seperate module and a button to assign it 
this macro and it does nothing. 

You were right about the 8 names and the work order numbers needed to start 
on k142 and l142 so you were right there. I really appreciate the help and 
time you took to write this. Now if I can just figure out how to make it 
work. Any ideas.



"Rick Rothstein" wrote:

> Try the following macro. I wasn't entirely clear on which sheets needed to 
> be processed (you said there were 7 sheets and then listed 8 names), so you 
> should check the names I used inside the Array function call on the fourth 
> line of active code where I assign the output from the Array function call 
> to the SearchSheet variable. Also, I wasn't sure where your "scheduled" and 
> "unscheduled" headers were on the 'WTD CHART DATA' sheet, so I assumed they 
> were in K141 and L141 with the data to be listed under them. There are 
> ranges that would have to be adjusted if that guess was wrong. Let me know 
> if you need help with that part. Also, the "scheduled" and "unscheduled" 
> headers are needed as my code needs to find their text when figuring out 
> where to put the "order number". Anyway, give this macro a try and let me 
> know how it works out...
> 
> Sub ProcessReschedules()
>   Dim X As Long, C As Range, FirstAddress As String, SearchSheet As Variant
>   Const SearchWord As String = "Rescheduled"
>   Const Destination As String = "WTD CHART DATA"
>   SearchSheet = Array("Monday", "Tuesday", "Wednesday", "Thursday", _
>                        "Friday", "Saturday", "Sunday", "Other")
>   On Error Resume Next
>   For X = LBound(SearchSheet) To UBound(SearchSheet)
>     With Worksheets(SearchSheet(X))
>       Set C = .Range("D:D").Find(SearchWord, After:=.Cells( _
>                         Rows.Count, "D"), LookIn:=xlValues)
>       If Not C Is Nothing Then
>         FirstAddress = C.Address
>         Do
>           If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
>                         C.Offset(0, -1).Value) = "scheduled" Then
>             Worksheets(Destination).Cells(Worksheets(Destination). _
>                Range("K141:K" & Rows.Count).Find(What:="*", _
>                SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
>                LookIn:=xlValues).Row + 1, "K").Value = C.Offset(0, 3).Value
>           ElseIf LCase(C.Offset(0, -1).Value) = "unscheduled" Then
>             Worksheets(Destination).Cells(Worksheets(Destination). _
>                Range("L141:L" & Rows.Count).Find(What:="*", _
>                SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
>                LookIn:=xlValues).Row + 1, "L").Value = C.Offset(0, 3).Value
>           End If
>           Set C = .Range("D:D").Find(SearchWord, After:=C, LookIn:=xlValues)
>         Loop While Not C Is Nothing And C.Address <> FirstAddress
>       End If
>     End With
>   Next
> End Sub
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "AwesomeSean" <AwesomeSean@discussions.microsoft.com> wrote in message 
> news:8D2A222B-7B6C-4137-9613-B02E7EC29FF4@microsoft.com...
> > Thank you for the reply. Here is a little better explanation (I think)
> >
> > I have a workbook with 7 pages I want to search for "Rescheduled" in 
> > column
> > D (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Other) 
> > If
> > "Rescheduled" appears in column D, Then look at column C to see if it says
> > "scheduled or unscheduled" if all this is true then I want the work order
> > number in column G on that same line to go to 'WTD CHART DATA' page in a 
> > box
> > I made where the upper left corner starts on K140 to L165 (2 column box)
> >
> > There are 2 columns in the box. K141 says scheduled and L141 says 
> > unscheduled.
> > I want the work order number (from row G on the other pages) to go in the
> > appropiate column.
> >
> > Here is the other part.
> > If there are already work order numbers in there I don't want to keep
> > duplicating them so if the number is already there then do nothing. If the
> > work order is not there then go to the next empty cell down.
> >
> > Did that help or did I make it worse?
> >
> > Thank you, Thank you, Thank you 
> 
> .
> 
0
Utf
11/25/2009 1:40:08 PM
Did you have any ideas?

Thanks again for the help. Looking at the code this should do exactly what I 
want. I just need to find out why it does not work at all. It does nothing 
with no errors.

Thank you
0
Utf
11/26/2009 11:42:57 AM
If an error is occurring, the On Error Resume Next is hiding it. Comment out 
this statement and the debugger should then stop on the line giving you your 
problem.

Also I noticed a typo in my code; however this typo will not affect the 
code's operation... I just perform the same test twice in an If..Then 
statement. This line of code...

          If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
                        C.Offset(0, -1).Value) = "scheduled" Then

looks like it should be this instead...

          If LCase(C.Offset(0, -1).Value) = "scheduled" Then

-- 
Rick (MVP - Excel)


"AwesomeSean" <AwesomeSean@discussions.microsoft.com> wrote in message 
news:4BB13219-08D7-4074-A8CF-FF813B5A7EC9@microsoft.com...
> Did you have any ideas?
>
> Thanks again for the help. Looking at the code this should do exactly what 
> I
> want. I just need to find out why it does not work at all. It does nothing
> with no errors.
>
> Thank you 

0
Rick
11/26/2009 4:45:58 PM
I deleted the on error line and fixed the other code and still nothing. I ran 
it in the VBA side and it just does nothing at all. Any other ideas?

Sean

"Rick Rothstein" wrote:

> If an error is occurring, the On Error Resume Next is hiding it. Comment out 
> this statement and the debugger should then stop on the line giving you your 
> problem.
> 
> Also I noticed a typo in my code; however this typo will not affect the 
> code's operation... I just perform the same test twice in an If..Then 
> statement. This line of code...
> 
>           If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
>                         C.Offset(0, -1).Value) = "scheduled" Then
> 
> looks like it should be this instead...
> 
>           If LCase(C.Offset(0, -1).Value) = "scheduled" Then
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "AwesomeSean" <AwesomeSean@discussions.microsoft.com> wrote in message 
> news:4BB13219-08D7-4074-A8CF-FF813B5A7EC9@microsoft.com...
> > Did you have any ideas?
> >
> > Thanks again for the help. Looking at the code this should do exactly what 
> > I
> > want. I just need to find out why it does not work at all. It does nothing
> > with no errors.
> >
> > Thank you 
> 
> .
> 
0
Utf
11/29/2009 4:21:01 PM
By the way, I am using excel 2003

if that helps




"AwesomeSean" wrote:

> Hello,
>    I am trying to come up some code to search a workbook for numbers and 
> then put them in a column on the last page but if the number already exists, 
> do nothing. I need it to do this everytime I run the macro. Any ideas?
> 
> here is an example:
> 
> look for "day" if true then i want the number in column j on that page to go 
> to the last page in column k, but if the number is already there then do 
> nothing. if it is not there then the next cell down.
> 
> Does this make any sense? I think I confused myself.
> 
> Any help is appreciated
> 
> Thank you
0
Utf
12/1/2009 7:23:01 PM
Reply:

Similar Artilces:

Customer Statement Crystal Report
Hi! Does anyone have a Customer Statement in Crystal format? Thanks -- Marisol Mortera Marisol, Your question is very generic. If you could be a little more precise, then I might have what you need. Girish "Marisol" wrote: > Hi! > > Does anyone have a Customer Statement in Crystal format? > > Thanks > -- > Marisol Mortera I am trying to build a crystal report for customer statements as well. The Great Plains one is very difficult to work with. If you have an example of this report in crystal, please email it at staceyleelee@hotmail.com Stacey &quo...

change date in a sql statement from a cell
Hello, I am using Excel 2007 I have a pivot table that gets refresh everyday. The data from the pivot table is based on a sql statement, which the data is connected to a AS/400 table. Here is my problem every morning I go in the connection properties and change the SQLstatement (date) in the command text. I don't want my user to do this. What other option can I do? I was thinking change the date in a cell (A1) and somehow the SQL statement picks up the new date or maybe some sort of parameter, but I am clueless in how to do this. Any tips or website to visit I will a...

Doing a VLOOKUP from the MIN value in an Array
This is a multi-part message in MIME format. ------=_NextPart_000_0082_01C37D23.15E40BE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Wow, this project has really opened my eyes to the fact that I need to = brush up on my excel (or learn Access!). Okay, I now have a value that shows me the lowest buying price of a = particular product. {=3DMIN(IF(C1:C3=3D1,B1:B3))} --- Thanks Dave Smith, you rock! --- Now I need a cell where I can put the location that sells that product = at the lowest price. =20 I have a spreadsheet showing the na...

SQL statement in form text control
Hello! In a social services DB, one of the reports is all incidents involving a particular client. There are a few thousand records. I am using an SQL statement which filters records by the client's full name and Birthdate. The SQL is sent to the rowsource property of a combo box. The combination of name and DOB provides 1 unique client. (or none if the DOB is mis-entered or is wrong) The report (controlled by a query with parameters provided by the DOB text control and the combo box.) The combo box seems to be just one more thing to click through, so I thought of changi...

Vlookup edited
Hello all you wonderfulhelp, Is it possible to avoid "NA" when using "vlookup" function. I need info only where it brings results. Thank you -- smile =IF(ISERROR(VLOOKUP(B1,C1:D4,2)),"",VLOOKUP(B1,C1:D4,2)) -- Gary''s Student - gsnu200851 Example of using ISNA rather than ISERROR which hides all errors. =IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE)) Gord Dibben MS Excel MVP On Thu, 7 May 2009 09:26:02 -0700, israel <israel@discussions.microsoft.com> wrote: >Hello all you wonderfulhelp, > >...

Vlookup Based on Multiple Conditions
Hi All, Is there any way to put a Vlookup formula with multiple criterias? I've over 45,000 rows in Col 'A' - 'E'. I want G1 to return value of D1, if (E1 and F1) = (A1 and B1) respectively and blank if they do not match. A B C D E F G 10 A X Emp1 10 A Emp1 10 B X Emp1 10 B EMP2 11 A Y Emp1 12 C Emp1 11 C Y Emp2 12 A X Emp2 12 A X Emp1 12 C Y Emp1 Thanks for your help. -- Karthi It doesn't seem to be a Vlookup job! =IF(AND(A1=E1,B1=F1),D1,"") Regards! Stefi „Karthik” ezt írta: > Hi All, > ...

Linking or Vlookup actual values
I have a workbook with 3 worksheets: Actual Weekly Units Ordered, Projected Weekly Units Ordered and Analysis. My column headings are set up by dates for all 3 worksheets, 1/2/2006 thru 12/25/2006. In the analysis worksheet, I need to pull in the Projected Weekly Units Ordered, however as the current dated week comes up and the Actual Weekly Units Ordered is entered, I need to have the Analysis Worksheet pull in the Actual Weekly Units Ordered for the then current week in lieu of the Projected Weekly Units Ordered for that week. I hope I made myself clear. Any help would be apprecia...

Simple Vlookup Looping
Hi Everyone, Cany anyone help me with writing a simple code to run every worksheet in my workbook and do a same vlookup? i.e. - I have 10 different worksheets in same format (same number of columns and rows as well) - Lookup value is always A5 in each sheet - Lookup range comes from different excel file called "finance.xls", "Summary" tab column A to B I am just trying to do 'Vlookup($A$5,'finance'A:B,2,false) in column B starting from row 1 to row 2200 for every worksheet. Please help Thank you for your help in advance. R...

What is wrong with this IF statement? need help.
This statement works fine but I want the cell to say 0" if the cell H6 has a zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14"""))))))) Pleas...

if/then statements with "counta"
I want to count the number of cells in a column that have a value less than x. Any tips? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200507/1 =countif(Column,"<x") "Patty via OfficeKB.com" wrote: > I want to count the number of cells in a column that have a value less than x. > Any tips? > > > -- > Message posted via OfficeKB.com > http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200507/1 > I replaced COLUMN with the range of cells and I replaced X with my value, but it didn't work. It returne...

VLOOKUP Across Workbooks
Hi all, Just a quick query... Is it possible to use VLOOKUP across worksbooks. I'm creating a Gantt chart in a new workbook, but need to reference dates in an existing spreadsheet. Let's say the workbook with the dates is called 'Plans' - for arguments sake. ;) TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=472044 OK - sorted it myself. Possible it is!!! -- S...

Download bank statement
How Can I download on line banking information from non - US bank that have the data available on Line? I am trying to do this with Italian Bank, but the list MS Money 200 offer is only for US institution. Fabio Fontana If the Italian Bank provide downloads, then you might just be able to try it and see whether Money accepts the import. If the bank isn't listed in the US list, it doesn't necessarily mean you can't use it. However, I don't think you'll get any support from MS if things go wrong, so try it in a test file first. -- Glyn Simpson, Microsoft MVP - Money Ch...

Adding "If Statement" with DSum
I am currently working with a database that needs a small adjustment to the following code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") This code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL ...

vlookup worksheet function for external excel files
I am using vlookup to retrieve data in an external excel file -i.e. the data I am trying to retrieve is not stored in the same workbook nor network drive as the records I am trying to find data for. Unfortunately, vlookup requires that the data source excel sheet file is open if it is to return records. Do you know if there is a way to retrieve data using vlookup while keeping the source closed. Thanks and regards, Gustavo I don't think =vlookup() has this requirement. Are you using =indirect() with =vlookup()? Gustavo wrote: > > I am using vlookup to retrieve data in an ex...

Getting address in Statement ID to appear on invoice.
I am trying to the address ID that is listed under Statement Address ID to appear onthe Bill To section of SOP Blank Invoice. I have tried mapping to the RM Statement Header Temp Table that has this information, but it doesn't appear possible. Is there a RW function to pull this information out? Thanks! Why don't they just put the statement address ID in the Bill To address ID of the SOP document? I'm sure I'm missing something here, but just wondering how the Bill To address ID field was 'repurposed'. "CC Account" wrote: > I am trying to the addr...

how do i use an if is null, and if is not null statement together
I have to compare two columns of data, and show the status in column three. Column1 = enrolleddate, Column 2= DisenrolledDate, Column 3 = Status If column1 and column2 are null, "Active", if column2 is not null, then disenrolleddate Thanks for helping me. If you were using a spreadsheet, you might need that third column. In Access you can simply use a query to do the comparison and generate the "calculated" value. Regards Jeff Boyce Microsoft Office/Access MVP "latha" <latha@discussions.microsoft.com> wrote in message news:A4366DA3-9E0E-48C8-BB30-452...

Vlookup query that checks 2 conditions
I've posted this before without too much luck so here it goes again... I'm trying to automate the creation of a vendor report that lists al vendor sales. Basically I need a formula that, based on a unique vendo number, will: - check colomn A for the vendor number match - then check column B to see if there is a buyer number (which mean that the item is sold) - then dumps then 'nth' occurance of the value / text from the column specify (8) I'm currrently using the formula: {=IF(Catalog!B2:B428 0,INDEX(VendorReportData,SMALL(IF(VendorReportData=$N$13,ROW(Catalog!$A$2:$...

Combo Box/SQL statement
I have a combo box whose Row Source Type is table/query and Row Source is "SELECT DISTINCT [Package Name] from Packages ORDER BY [Package Name];". I can see the values when I run that SQL statement as a query, but nothing shows up when I run it as a function of the form (i.e., the form opens up, the SQL statement runs, but nothing shows up in the combo box). I mean literally, nothing shows up, although I can see there are the three place holders in the list I'm expecting, but no text. I'm wondering how much effect the Bound Column property has on this, and frankly, from th...

complex Vlookup question
Hello, I'm trying to do the following. State Country New York USA Jan 09 Populations 1.5 mil Rio De Janeiro Brazil Pouplation 2.5 mil How would I do a vlookup where I'm matching the name New York and I will need to pull the population (1.5 mil)Typically when we do vlookup, it usually matches the Name and than looks for the column index and in my case I would get Jan 09 if I put column index of 3. So, how do I pull the populations based on the name? I tried using sumproduct and it works...

Count Function on True Statements
I wonder if I can do a count on a specific value, like if I had this: CAR1 CAR1 CAR2 CAR2 CAR2 CAR3 Is there a way I can count how many "car2" I have? Currently, the only way I can do this is to set a true/false statement then count the trues. -- krayziez ------------------------------------------------------------------------ krayziez's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34891 View this thread: http://www.excelforum.com/showthread.php?threadid=546334 =countif(a1:a10,"car2") This is a function that accepts wildcards, so if y...

VBA Vlookup
Hello, I need a VBA formula that would execute a Vlookup between 2 workbooks. However, the range for the Vlookup starts from a row that has a text, but never has the same row number. In other word: it is possible to have a Vlookup with a range starting from a referenced row that moves all the time ? Jeff Give the referenced row a name through Insert>Name. Now, in the code refer to the row through it's name and Excel will dynamically locate it. "Jeff" wrote: > Hello, > > I need a VBA formula that would execute a Vlookup between 2 workbooks. > However,...

SQL statement help #2
I would like to add this to the following statement: inactive = no SELECT * FROM Item WHERE HQID = 0 Thanks in advance lax guy The script would be changed to SELECT * FROM Item WHERE (HQID = 0) AND (Inactive = 0) -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax) "lax guy" <laxguy@discussions.microsoft.com> wrote in message news:8DEA6EDC-4A78-4039-B6E2-AC874AF46E94@microsoft.com... >I would like to add this to the following statement: inactive ...

Conditional statement with mail merge
Hi, I'd like to do a mail merge (in Pub 02) where I test the contents of the merged field and based on the results print either the contents of the database or some other preset text. I know this is possible in Word, is it possible in Pub 02? Thanx for your help. Gary Noooo, most definitely not available in Publisher. -- "If you don't know where you are going, any road will take you there!" Nuts! But thanx for the answer, now I can stop looking for it in the help files >-----Original Message----- >Noooo, most definitely not available in Publisher. > &...

VB
I have a query which displays contents in a grid. The SourceObject Property is "Query.qry-ShowData". How can I return the actual SQL statement used by this query? The reason is this query pulls contents from different forms to create the WHERE statement and I would like to see the actual SQL statement used by the "Query.qry-ShowData". Thank you in advance for your help. Mike Hi, try: debug.print currentdb.querydefs("qry-ShowData").SQL -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "Michael Kintne...

Looking for a Sql Statement to delete items never sold or received
Need help to delete some items from the database that are just using up space. When we first got RMS someone had the bright idea just to load information from our suppliers. The gave us so much stuff that some has never been used. Just look for a quick way of deleteing this useless stuff from the database. Items that have 0 quantity never sold or been received since created first off, always make sure you create a backup of your DB before running any queries, in case you need to restore back to that point you can run this select query: SELECT * FROM Item WHERE Quantity = 0 AND LastRe...