vlook on different tabs based on value

good day,

i have the following so far in my macro:

.... 
supcode = Range("B4").Value
.... 
    If supcode = "CLI" Then
        ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order 
Generator.xlsm]CLI'!C2:C4,2,FALSE)"
.... 

right now i have this code repeated several times for each supcode that may 
exist and i am usingn an if statement for each possible "supcode".  i would 
like to replace all the if statements, and change the "CLI" to use the 
"supcode" value.

how do i get the "supcode" value into the vlookup code?

thank you,

jat
0
Utf
5/5/2010 5:46:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1070 Views

Similar Articles

[PageSpeed] 56

'All on one line
ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order Generator.xlsm]" & 
supcode & "'!C2:C4,2,FALSE)"

-- 
Best Regards,

Luke M
"jatman" <jatman@discussions.microsoft.com> wrote in message 
news:4162A244-900C-40FB-9032-042A66872305@microsoft.com...
> good day,
>
> i have the following so far in my macro:
>
> ...
> supcode = Range("B4").Value
> ...
>    If supcode = "CLI" Then
>        ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order
> Generator.xlsm]CLI'!C2:C4,2,FALSE)"
> ...
>
> right now i have this code repeated several times for each supcode that 
> may
> exist and i am usingn an if statement for each possible "supcode".  i 
> would
> like to replace all the if statements, and change the "CLI" to use the
> "supcode" value.
>
> how do i get the "supcode" value into the vlookup code?
>
> thank you,
>
> jat 


0
Luke
5/5/2010 5:48:44 PM
Reply:

Similar Artilces:

Send Email Account based on Recipient
I'm using Outlook 2003 I have 5 POP3 accounts and I would like to automatically bind different email addresses (or contacts) to different Email accounts when I compose a new email message without having to select the sending account from the drop-down menu. Is it possible? Thank you. See reply to your other post. Jay Randon wrote: > I'm using Outlook 2003 > > I have 5 POP3 accounts and I would like to automatically bind > different email addresses (or contacts) to different Email accounts > when I compose a new email message without having to select the > sendi...

Different Prices in Different Stores
I run 8 stores, and HO and want two of the stores to have different prices to the other stores. We handle all pricing from HO. I've never done this before, and although I can read the manual, it's often better to ask the people that have done it for their experience, what mistakes have been made, and what to look out for. thanks Chris England Style 304 Change Item Prices (regular) worksheet, check mark the stores you want affected by those prices, so the worksheet gets created just for those two stores...if all items will have a different price, select ALL DEPARTMENTS so a...

Dumb Question: Difference between Reconciled and Cleared
Hello All! Dumb Question: In Money 2003, in the account register, there are choices to mark an entry as 'cleared' or 'reconciled'. To me, they both mean the same thing.... Maybe it's the way I think of calling them, but what is Money's way using these definitions? How should I use them? Hope I didn't confuse you all! Thanks in advance!! Rick D. Gee, seems like we did this thread just a day or two ago. Did you look? Cleared=will be shown as cleared the next time you go to balance. Reconciled=will be considered part of the opening balance next time you g...

Passing value from Form to Report
Hi, I have a form that contains a drop-down menu that allows the user to select an employee currently in the database. Upon selection of an employee, the employee's information (address, birth etc) show up on the form to allow the user to modify said information. There is a button on this form that should bring the user to a report on the sales performance of the selected employee. So here is my problem. The data for the employees personal information and the employees sales records are in two separate tables. How do I get the report to actually show the selected employee's sales...

Sort Page Tabs into Alphabetical Order ? ?
Hi All Just a quick one, is there anyway to sor Excel Pages into alphabetical order ? Any Suggestons would be great Dodge From a previous posting:- http://groups.google.com/advanced_group_search?hl=en http://www.rondebruin.nl/Google.htm Sub SortALLSheets() 'modification of coded example by Bill Manville Dim iSheet As Integer, iBefore As Integer For iSheet = 1 To ActiveWorkbook.Sheets.Count Sheets(iSheet).Visible = True For iBefore = 1 To iSheet - 1 If UCase(Sheets(iBefore).Name) > UCase(Sheets(iSheet).Name) Then ActiveWorkbook.Sheets(iSheet).Move...

Sharing but tabs are missing
I am using Outlook 2003 and I want to share my calendar. When I click on Share my calendar, I only see 3 tabs, none labeled Permissions. My co workers see about 7 tabs with one labeled permissions. did I delete tabs from my calendar properties somehow? lenoir;146600 Wrote: > I am using Outlook 2003 and I want to share my calendar. When I click > on > Share my calendar, I only see 3 tabs, none labeled Permissions. My co > workers > see about 7 tabs with one labeled permissions. did I delete tabs from > my > calendar properties somehow? Are you using ex...

query-based distribution group not showing in Outlook
Software: Exchange 2003 standard and Outlook 2003 I have created a query-based distribution group and it isn't showing up in Outlook Address Book. If I create Distribution groups they show up in the Address Book. How do I get the query based groups to show in the Address Book? Under Exchange Advanced I have confirmed that the check box to hide list is unchecked. Thanks, Colin M. On Tue, 27 Dec 2005 10:54:52 -0800, "Colin" <legendsfan@nospam.nospam> wrote: >Software: Exchange 2003 standard and Outlook 2003 > >I have created a query-based distribution ...

Vlook in VBA with If statement
I need help to run aVBA formula to execute the following: Do a vlookup of the value in row “A3”in wooksheet “Billable Jobs” in the worksheet “VBAP” column “D” if and only in worksheet “VBAP” column C the cell value is “ZA” -- Regards, Jeff Addional info -- Regards, Jeff "Jeff" wrote: > > I need help to run a VBA formula to execute the following: > > Do a vlookup of the value in row “A3”in wooksheet “Billable Jobs” in the > worksheet “VBAP” column “A” - to take the value in column "D" - if and only in worksheet “VBAP” column C the cell >...

Different questions
Hi, All I have a few questions with regards to CRM in the financial vertical and does anyone know of a partner who has specifically customzied CRM for the financial secotr? 1. Can configurable activity "result" codes be used to capture the result of each sales step? 2. Can configurable "Sales Stage" data fields be used to track where each opportunity stands in the sales pipeline? 3. Does the system have functionality that will allow the planning and tracking of investor roadshows inc. Timings, clients seen, comment, success/outcomes? 4. Can (and how) the system be us...

Can you sum multiple values within one cell?
I have a simple question: I am entering amounts for invoices and in some cases, the invoices may have multiple account numbers and associated dollar amounts. What I have been doing is pressing ALT +ENTER to have the cursor return down one row, and then I enter the associated amount. So the record looks something like this? A3 A4 12.50 63-400-210 36.75 65-200-200 The 12.50 and 36.75 are in one cell, say A3 and then the associated accounts are in A4. I am wondering if there is any way that I can have the 12.50 and 36.75 added up, preferably at the bottom of the sheet, such ...

If with Vlook up
Hi Everyone, I have a spread sheet that is exported from access some of the cells have a numeric value which should really be text - what I would like to do is run a macro or code that will look at the cell and return a text value for the given number, this would need to be repeated for 13 coloumns and hundreds of rows Many thanks in advance If each column is supposed to look the same way (maybe all the entries are 8 characters with leading 0's), you could use a helper column with formulas like: =text(a1,"00000000") And drag down Then you could copy that helper column a...

"Automating Word document for different versions of Word"
I am developing an app (in VB 2008) that will open a Word template, populate some bookmarks, save the document as a Word document and then allow the user to access the Word document and add their own info. My problem is I don't know now (while I'm developing the app) what versions of Word the user will use - they have a mixture of Word versions (2002, 2003, 2007) so how do I develop for them all - I understand I must have a reference to the Word library but I don't know which one(s). Any help will be gratefully received. Regards and Thanks Alan "alanbr&qu...

2 different accounts
Me and my wife, we have both an email account, at the same server. My wifes computer is broken and I would like to make also for her a separated account in my windows mail on my computer. I have made a new account for her emailadress but now all her mails come in my mailbox. Is there a solution to split the accounts?? You can have up to 32 email accounts in Windows Mail. However, Windows Mail does not have identities like Outlook Express did. By default, all email from all accounts goes into the same Inbox.=20 There are four different ways of changing that: 1. Use separate Windo...

-ve values in Y Axis
Hi, I gave a graph with date values on X-Axis & values from -50000 to 300000 on Y-Axis. Currently my data labels on X Axis are seen touching accross the '0'th position of Y axis (i.e. data labels are comming in middle of my graph on 0th position across y axis). I would like to move down the data labels to appear on x axis touching y axis at -50000 point instead of 0th point. Can someone help me here? Double-click on the primary axis. In the Format Axis dialog box, go to the "Scale" tab. In the minimum value input, type in -50,000. -- John Mansfield "...

How do I ignore Graphs zero values.
I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in. select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" <fishy@discussions.microsoft.com> wrote in message news:96CA651F-D05F-4211-B94C-105616305CFE@microsoft.com... >I have a variety of tables in excel that sum approx 20...

Searching for different strings?
I have a formula that currently searches for the string "risking" and enters the amount found after it into the corosponding cell in colum A =IF(ISERROR(SEARCH("risking",$G31)),"",VALUE(MID(G31,SEARCH("risking",$G31)+ 8,SEARCH("to win",$G31)-SEARCH("risking",$G31)-8))) I would like to ammend this to seach for "risking" or "withdrawal". I can't seem for figure out the proper formatting I have tried =IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris ...

automatically creating selected number of records in different table
Hi, I am setting up a database to track insurance policies issued, and I've hit a wall with one item. Each policy can have up to 10 classes, and each class can have an unlimited number of benefits. I have a table for the policies that currently links to a table for the classes, and that links to a table for each benefit applicable to each class. To enhance this, I'd like to add a field to the policy table where the underwriter enters the number of classes (x), and then x number of records is automatically created in the classes table (and each record that was created is numbered co...

Calculating the Difference in a Query
I need to calculate the difference between two fields in a query. The query is called “qryRemote” and within the query are the fields “10-8Time” and “10- 97Time”. The data entered into these fields are in the format “0000” through to “2400”. I need to subtract the “10-8Time from the “10-97Time” and then sum the results and display the result in a report. For example, after the query is run the results are: 10-8Time 10-97Time --------------------------------- 1307 1331 1858 1909 2018 2023 First the difference is determined: 10-8Time is subtracted form t...

Difference of the averages of two ranges
Can someone explain mathematically why the difference of the sum of the average of row two and three (Range D2:D3) as shown in cell E3 is different than the average of each individual difference in range A4:C4 as shown in cell E4. It is not obvious to me. test 1 test 2 test 3 Average Diff 10 5 10 8,33 6 1 2 3 -64,00 % -40,00 % -80,00 % -80,00 % -66,67 % -- Regards Tommy On May 18, 12:35=A0pm, Tommy <To...@discussions.microsoft.com> wrote: > Can someone explain mathematically why the difference of the sum of the > average of row two and three (Range D2:D3)...

VLook Up issue
Hi All hopefully a straightforward question...I'm using the following kind of lookup in a work book, looking up a value between two sheets. IF(ISERROR(VLOOKUP(.....),"0",VLOOKUP(...))) The problem isn't the formula, it's that when I copy and paste value the range to look up in, I have to literally go into each cell, press F2 and enter till the end of the range of data for the formula to work, otherwise I just get a 0. I've tried changing the formatting of the cells etc, also tried using "Trim" and substitute, both to no joy, any help would be appreciated...

Control instance from different framework version.
Hi, A managed dll file is injected into another managed process. The injected process use a different version of the .Net framework version. In my DLL I want to do something like : Control ThisCtrl=Control.FromHandle(6666); Because the two processes use different framework version we CANT do that. My dll will always be the highest framework version vs the injected process. Quetion: Is there a way to get an instance of the control even if the control come from another framework version.? A kind of tolerance to Object from previous framework? By the way "Contro...

SAME DOWNLOAD DIFFERENT DAY #2
THIS A REPOST FROM A PREVIOUS DAY. I have one bank account set up for online download of transactions. Once a month, we they tack the interest charge on to the account I download it. This same transaction continues to show up as a new transaction for the nextfew days. Within a few days I will receive the monthly statement. I then balance the account and reconcile all transactions on the statememt, including the monthly interest. For a few days after I have reconciled the account I continue to download the same interest transaction that has now been coded as reconciled. I do not change ...

Determining Display Area of Tab Control in MFC
Can anyone help me understand the behavior of the following code? This code is part of an MFC application and CDialogTabCtrl is a class derived from CTabCtrl. m_arPanels is an array of pointers to CWnd objects that correspond to each tab of the tab control. The following code seems to work fine but I'm not sure why. The problem is the call to MoveWindow. The docs say that the right and bottom members of the structure specify the width and height of the window. But when I uncomment the two lines that convert the right/bottom to the width/height, I get a larger margin along the right and b...

Conditional formatting applied to a different cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I use an Excel spreadsheet at work to track and prioritize my workload. <br><br>The entries on the sheet are first sorted by date received and then by RMA number (columns D and A respectively in my spreadsheet). <br><br>I want to apply conditional formatting such that if the RMA is at least 60 days old, I want the RMA number is highlighted in blue, and if over 100 days old, then the RMA number needs to be highlighted in red. <br><br>What is the proper structure for the conditi...

Unique Values + UNION = ERROR!Q
I've searched here and on-line and not found any mention of this. I'm using Access 2002. I create Query1 that selects DISTINCT (Unique Values = Yes), returns two fields, uses date range values in controls on a form as selection parameters, and uses three values ("Green" or "Red" or "Blue") as another selection criterion. I build a Query2 that does something similar. I try (keyword) to create a UNION query that returns all the rows found in Q1 and Q2 (e.g.,: SELECT * FROM Q1 UNION SELECT * FROM Q2; but Access folds and I get to send ...