change the code to be a formula

i have the following code added to the s/s tab which does what i want but is 
there a way of adding this to cell like a formula thing like vlookup??

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
  If Target.Value = "" Then Exit Sub
  Application.EnableEvents = False
  Target.Value = Worksheets("Station").Range("A1") _
    .Offset(Application.WorksheetFunction _
    .Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0)
  Application.EnableEvents = True
End If
End Sub

thanks pete
0
11/30/2005 5:38:07 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
467 Views

Similar Articles

[PageSpeed] 16

First, your code actually changes the value of the cell after you type in your
data.

You won't be able to do that using a formula (well, unless you type the formula
in that cell).

You might be able to use a formula in an adjacent cell to return that same
value, though.

Something like this in C7:
=OFFSET(Station!A1,MATCH(B7,StationList,0),0)

If that doesn't work, you may want to post the address of StationList.  It'll
make testing a bit easier.


Little pete wrote:
> 
> i have the following code added to the s/s tab which does what i want but is
> there a way of adding this to cell like a formula thing like vlookup??
> 
> Option Explicit
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count > 1 Then Exit Sub
> If Target.Column = 2 Then
>   If Target.Value = "" Then Exit Sub
>   Application.EnableEvents = False
>   Target.Value = Worksheets("Station").Range("A1") _
>     .Offset(Application.WorksheetFunction _
>     .Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0)
>   Application.EnableEvents = True
> End If
> End Sub
> 
> thanks pete

-- 

Dave Peterson
0
petersod (12005)
11/30/2005 7:29:22 PM
Reply:

Similar Artilces:

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

Exchange 2000 DST Changes
How is one supposed to address the DST changes for Exchange 2000 if they do not have extended support to get the patch? Will Microsoft be providing a workaround like they did for Windows 2000? I'm referencing the following article: http://support.microsoft.com/kb/930879 It states : What to do before you run the Exchange tool Install DST updates Before you run the Exchange tool, make sure that client and server computers are updated correctly with the operating system and application DST updates. These updates must be installed in the following order: 1. Install the Windows DST upda...

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Changing Item #'s
Is it possible to change the item #'s after the item has already been entered? TMM: No there isn't in the "base" product, but MBS does have a tool that you can use called Item Modifier - this will allow you to change an Item number from one value to another. You can email this address below and they can answer any questions you have about the tool and can tell you what the cost of it is. mbsprofessionalservices@microsoft.com Hope that helps you out, JG "TMM" wrote: > Is it possible to change the item #'s after the item has already been entered? ...

Vb.net 2008 ContextMenuStrip logical error when running code
Greetings, I have a connectmenustrip item that when clicked runs the following code (see below) Now if the event is called by the button i.e. cmdDeleteingBooking.Click the linq query returns the appropriate value. However when called by cntMnuCancelBookingItem.Click is returns 0 even if a checkbox is of 'TRUE' value. Debugging shows the code runs exactly the same code (which loops around rows in a datagridview checking if the checkbox has been checked). Could someone explain the reasoning why the same code would return different results? Private Sub cmdDelete...

RMS Status Codes
Just wondering if anyone has a list of what the RMS Batch.Status codes 0-15 mean? I can't find them defined anywhere. I'm specifically looking at how to identify Blind Closes so I don't count them in totals until they'e been closed. Thanks! -Zim There is a Knowledge Base Article that covers the different Batch Status codes from 0 - 31. Just search for 'batch status codes' -- Robert Armstrong RMS Systems Inc. www.retail-pos.com "Zim" <Zim@discussions.microsoft.com> wrote in message news:C72515DB-AD45-4C7D-B8DE-0A18E4A6D0D0@micr...

Excel worksheet with VBE codes don't work elsewhere
Hi, Some of my excel worksheets with embedded controls and VBA codes don' work when I open it on another PC. Is there another way to make i work? Thx -- lazybea ----------------------------------------------------------------------- lazybear's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3519 View this thread: http://www.excelforum.com/showthread.php?threadid=54955 Specifically what problems are you having? Saying "don't work" means absolutely nothing. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ww...

Trouble doing a formula for excel
Hi All I have a spreadsheet with the following A1: z:\data/pc32/tsheets\unsorder00039.csv I would like to add 1 too the number to make unsorder00040.csv and so I have try mid,right,left i can't seem to do it Cheers "Jason" <Jason@discussions.microsoft.com> wrote in message news:53AAB904-8595-499F-BF38-8BE00826101C@microsoft.com... > Hi All > > I have a spreadsheet with the following > A1: z:\data/pc32/tsheets\unsorder00039.csv > > I would like to add 1 too the number to make unsorder00040.csv and so > I have try mid,right,left i can't see...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Excel formula #24
What is the formula that brings back a zero for an empty cell instead of 0 0 #DIV/0! Try =if(iserror(formula),0,formula) ************ Anne Troy www.OfficeArticles.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:8392DE7F-0B65-4DEE-87F4-985133BB1976@microsoft.com... > What is the formula that brings back a zero for an empty cell instead of > 0 0 #DIV/0! > ...

A student figuring a formula for wages
Hello, I am beginner student trying to figure out a formula that calculates how to pay people for the number of hours they work, and at the same time figure out any overtime they may have. The wage is in cell B4, and the # of hours is in cell C4. Overtime is figured at 1.5 of the wage in B4. I must put the end result in F4. The instructor rushed through his presentation, and said to use the "if function". The assignment is due on tuesday and any help with this is greatly appreciated. -Thanks in advance ------------------------------------------------ ~~ Message posted fro...

Changing named range reference depending on a cell's content
Where to start?! I've got the following formula pulling data in from a secon spreadsheet within the same workbook: =IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0) We have 8 different locations ("MICH" being one of them) that we nee to be able to access. I can write a nested IF formula that looks a cell I7 (that contains a list of all 8 locations) and, depending o I7's content, brings back the desired values. I was hoping someone in the forum could help me write a simpler formul that would not have 7 IF statements embedded in it. Any help w...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

Stop changing of languages in Word 2003 when typing letter.
While typing a letter in Word 2003 it changed from English to what looked like Arabic language. I have rebooted 3 times and started over and still the same occurs. I even copied what I had in English to an email in Yahoo and sent it to myself and rebooted. Came back to that same email and copied it to a new email that I started to myself, and when I started typing it would only type in what looked like Arabic language. This happened to me once a couple of weeks ago, but never occurred again till now. What do I do? In the Language dialog box, turn off the option to autom...

Strange behaviour: show/hide formatting symbols reveals old change
In Word 2007, I'm getting some strange behaviour in a document that was authored by someone else. Track Changes is switched off, all changes have been accepted, and everything looks as it should in whichever view I happen to choose (Print Layout, Draft, whatever). But when I click to show formatting symbols (in whatever view) a whole lot of old changes - deletions AND insertions, ostensibly all accepted, and from before the document got to me - appear in the document, making it quite tricky to work with. These old changes are impervious to anything I try to do with them E...

Creating formulas to calculate time on a 24 hr time clock
I have to create a spreadsheet that will calculate total hrs worked. I've tried several different ways but I guess I don't know enough about building formulas to actually make the thing work. For example, I used a basic formula D3=A3-B3+IF(A3>B3,1)*12. This works to show the time but not the number of hours worked. If an employee comes in 1/2 hour late, it does not properly calculate this. How can I correct my formula? MW I have tried your formula and it works fine (with start time in B3 and finish time in A3). Format the reult as h:mm and it's OK. Andy "MWI...

Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or higher.either text or cell shade. just so it catches the users attention. im running excel 2000. and i have this currently in the cell that i want to aplly this to: =HLOOKUP(D20,'Hidden Data'!GZ10:HB11,2,0)*MAX(15,E20) have you tried conditional formating? format>conditional formating >-----Original Message----- >I need a cell to change color if the value inside the cell reaches 50 or >higher.either text or cell shade. just so it catches the users attention. >im running excel 2000. and i ...

Date/Time Show When Changes Made
Hi, When changes are made in a table or form, I want the date and time to appear showing me when changes were made in any of the fields. How can it work? MTIA. It's not possible if you're working directly with tables. Using foms, you have to put code into the form's BeforeUpdate event to update the row's LastChanged field (which, of course, you have to add to the table yourself) You might find what Allen Browne has at http://www.allenbrowne.com/AppAudit.html to be useful. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Beame...

OWA and password change
I have OWA in the DMZ but the mail server is behind the DMZ. I have no problems accessing OWA but I do have a problem see the users on my DC. The real issue is that I am trying to have users change their own password within OWA and need to know what ports I need to have open without any security breach. I need to know what is safe to open and what's not safe to open. Thanks. Hi WooYing, have you seen this articel s? http://support.microsoft.com/kb/179442 http://support.microsoft.com/default.aspx?scid=kb;en-us;259240 http://www.microsoft.com/technet/security/guidance/secmod44.mspx F...

formatting of charts changes when copying from excel 2000 to 200.
When I copy a chart from Excel 2000 and paste it into Excel 2003, some of the formatting is lost. In particular, scale and axis formatting. Is this a programming issue or can it be corrected easily. Thanks Hi, First one would answer why would you copy charts from 2000 to 2003, why not make them in 2003? Second and more important - how are you copying them - there are maybe 20 possible methods of copying a chart from one program to another. Please tell us exactly which steps you use to do the copying. Also, exactly what formatting are you loosing, what do you get instead? When ...

Inventory Reconcile SQL program code
I have been informed by Microsoft that the IV Reconcile procedure uses Dexterity to perform the calculations and inventory quantity updates -- and not SQL stored procedures. We have a large implementation of Great Plains with 650,000+ Inventory titles and use Mfg. The IV reconcile process is taking multiple days to run, and is cumbersome because it locks users out of Sales Trx entry/POP and IV. Has anyone made an attempt to write the SQL scripts necessary to update the IV tables for allocated quantities, qty on backorder, qty on order, etc? btw, we also have Multi-bin installed. ...

change chart title with auto filter
I would like the title of my chart to change automatically every time I use the autofilter function to make a new selection of data to be displayed in the chart. Can anyone inform me of how to make the chart title change automatically with each new selection? Thanks, aja Hi, See this 'link' (http://www.tushar-mehta.com/excel/newsgroups/dynamic_chart_title/fp_tutorial.html) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20138 View this thr...

Code Stopped Working Help
Hi All, A Few Months ago i had some help and creating some code that automatically generated a number for each project which was recieved, its been working fine untill We hit the Year 2008, The Code Made up a Value for example Q586707 Q586807 Q586907 < Last Number for 2007 The Code Then Was Suppose to Start the first number off as Q000108 which it did, and no when i open the wizard i created it keeps generating that number its not moving up to Q000208 and you see the number is made up of 2 parts Q0000 08 Heres the code and its on the forms Current Event Procedure Private Sub Form_...