The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks!

The code below worked for a few days and just totally stopped working.
What can I do to make sure it continues to work? Thanks!

-----



Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("A:A"), Target)
If D Is Nothing Then Exit Sub
For Each C In D


On Error Resume Next
Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column E
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column F
Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.!
R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,I.O.!R2C1:R5708C5,4,FALSE))"
' - For Column J
Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Mellon
Download'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Mellon Download'!
R2C1:R4573C3,2,FALSE))" ' - For Column K
Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.!
R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,I.O.!R2C1:R5392C5,5,FALSE))-
IF(ISNA(VLOOKUP(RC8,'Mellon Download'!R2C1:R4573C3,3,FALSE))=TRUE,
0,VLOOKUP(RC8,'Mellon Download'!R2C1:R4573C3,3,FALSE))" ' - For Column
M
Target.Offset(0, 13).Value = "1" ' - For Column N
Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
Target.Offset(0, 15).FormulaR1C1 = "=TODAY()" ' - For Column p
Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
[-1])" ' - For Column Q

Next C

End Sub
0
Damil4real
11/24/2009 10:50:38 PM
excel 39879 articles. 2 followers. Follow

1 Replies
951 Views

Similar Articles

[PageSpeed] 45

Make sure that events are enabled.  Try this sub

Sub TurnOn()
Application.EnableEvents = True
End Sub

-- 
HTH,
Bernie
MS Excel MVP


"Damil4real" <damil4real@gmail.com> wrote in message 
news:abea896f-d49d-46b1-bc2d-2eaee10d022c@h10g2000vbm.googlegroups.com...
> The code below worked for a few days and just totally stopped working.
> What can I do to make sure it continues to work? Thanks!
>
> -----
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim C As Range, D As Range
> Set D = Intersect(Range("A:A"), Target)
> If D Is Nothing Then Exit Sub
> For Each C In D
>
>
> On Error Resume Next
> Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column E
> Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column F
> Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.!
> R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,I.O.!R2C1:R5708C5,4,FALSE))"
> ' - For Column J
> Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Mellon
> Download'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'Mellon Download'!
> R2C1:R4573C3,2,FALSE))" ' - For Column K
> Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
> Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.!
> R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,I.O.!R2C1:R5392C5,5,FALSE))-
> IF(ISNA(VLOOKUP(RC8,'Mellon Download'!R2C1:R4573C3,3,FALSE))=TRUE,
> 0,VLOOKUP(RC8,'Mellon Download'!R2C1:R4573C3,3,FALSE))" ' - For Column
> M
> Target.Offset(0, 13).Value = "1" ' - For Column N
> Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
> Target.Offset(0, 15).FormulaR1C1 = "=TODAY()" ' - For Column p
> Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
> [-1])" ' - For Column Q
>
> Next C
>
> End Sub 


0
Bernie
11/25/2009 1:29:14 PM
Reply:

Similar Artilces:

Multiple copies of just one email
I'm using Outlook 2002 on Windows XP professional and I keep receiving a new copy of one email message every time I open Outlook or it performs a timed send/receive. All other email messages have been fine, i.e. just one copy. Would this be a problem with my computer that I can fix, a problem with the sender's computer that she can fix, or is something else the matter? I only get one copy of all other messages from the same sender. If anyone else is having this problem, I received an answer in the Office-Outlook-General Questions Discussion Group. I was able to log into my ...

Can I fix the exponent in scientific/engineering format?
This is sort of a continuation of my search to find a ppm number forma (http://www.excelforum.com/showthread.php?t=368806). Found someone who mentioned using engineering formats (kind of a extension of scientific format). After a little tinkering, here's wha I've found (using the values of 7E-3 and 7=-5 as examples): format 7e-3 displayed 7e-5 displayed .0E+0 .7E-2 .7E-4 0.0E+0 7.0E-3 7.0E-5 (REGULAR SCIENTIFIC NOTATION) #0.0E+0 70.0E-4 70.0E-6 (EXPONENTS ARE MULTIPLES OF 2) ##0.0E+0 7.0E-3 70.0E-6 (EXPONENTS ARE...

Can i use Windows Search to find a number within Excel?
If i use Widnows search of the CONTENTS of files (not file names), i find in Excel, that for cells that contain text, they can be found. But, it does not appear to me that numbers can be found. Of course the number as Excel stores it may differ from how it is displayed. But, does anybody know if this is possible? For instance, i have a cell with the number 3,709,227 in it, as it appears in Excel (as formatted). I tried typing the number both with and without the commas, into WIndows search, but Windows search still does not find the Excel file that contains that number. So, any kn...

How can i asign MBF roles to user accounts?
Hello. I want to asign to user accounts the next roles: MBF roles: - Order Management Customer - Customer (Advanced role) I was wondering how can I make this Thanks for your help Jose Ponce Aguilar ...

Code and Creation 53161
This is a free and informational site I created for application development and application development source on the net. Enjoy, and it's free, nothing to register oir sign up for just an informational site. http://codeandcreation.com z<Vqdz#Q!e,IP!+zGZu<KLohA ...

Paying a lot of money for a formal dress that you will only wear once can be frustrating. Used clothing stores can offer an extensive selection of formal wear at affordable prices that you or your d
Paying a lot of money for a formal dress that you will only wear once can be frustrating. Used clothing stores can offer an extensive selection of formal wear at affordable prices that you or your daughter will love. http://www.shoesbootjeans.com http://www.shoesbootjeans.com/Replica_Mens%20Shoes_1.html http://www.shoesbootjeans.com/Replica_Womens%20Shoes_1.html http://www.shoesbootjeans.com/Replica_Boots_1.html http://www.shoesbootjeans.com/Replica_Boots_1.html http://www.shoesbootjeans.com/newarrivals.html http://www.shoesbootjeans.com/hotproducts.html Used clothing stores are full of ite...

Newbie q: Can't modify a database
Hi, I've been handed an access database in order to modify it (in the programming sense), but when i open it it opens as an application, without any apparent means of modifying the database structure. All it will let me do is add or edit data. Is there a secret trick to opening the database for modification, or do I have to go to the owners of the database and have them save it in a different way? Thanks, Jon Hi Jon If you hold down the shift key while the database is opening it should bypass any startup options code and take you straight to the database window. If this doesn'...

Can I have two Internet Mail Services in one Exchange 5.5 Organiza
Hello, My current environment consists of 6 mailbox servers and one server hosting the internet mail service (Exchange 5.5 on Windows 2000). I need to use one of the 6 mailbox servers for a product that will need to route outbound mail to another server for other secure outbound services while the other 5 servers route to the current internet mail service. Is this possible? Can I add a secondary internet mail service just for this one server? I am not able to find any knowledge base articles for assistance. Thanks in advance! On Tue, 28 Jun 2005 15:36:10 -0700, "exadm" &l...

Make sure no running instances of MS Word
I need do some work which do not want any instances of MS Word existed. How to make sure no running instances of MS Word? Use CreateToolHelp32SnapShot(...)/Process32first/Process32Next and see if the PROCESSENTRY32 's szExeFile has winword.exe substring. -- Vipin Aravind Microsoft MVP "lotus" <hanfeifei@gmail.com> wrote in message news:1137401872.309784.308260@g47g2000cwa.googlegroups.com... >I need do some work which do not want any instances of MS Word existed. > > How to make sure no running instances of MS Word? > ...

Exchange 2003 OWA stopped working and DNS Events
Hi All, I am struggling with yet another Exchange 2003 problem. It has been working flawlessley for almost, nothing changed and all of a sudden quit working. When I try to open up my OWA I am getting the 503 service unavailable page. In my Event Logs, I am seeing this when I reboot the server: The dynamic registration of the DNS record '_ldap._tcp.Default-First-Site-Name._sites.ForestDnsZones.ic-MYDOMAINXXXX.us. 600 IN SRV 0 100 389 exchange.MYDOMAINXXXX.us.' failed on the following DNS server: DNS server IP address: 205.178.190.31 Returned Response Code (RCODE): 5 Returne...

highliting works on with html format?
Hi guys, Here is what I would like to do: In outlook, suppose I get a message in plan text format. I would like to open the email, and maybe make some sentences bold and colored. and then I can save it. Hence after sometime the email will be noticable for me. Now, I can do this if the e-mail is sent in html format cause then I open the message and then I press "edit message" and the edit toolbar becomes usable and I do things on the email. However, if the email is sent in "plain text" format, I can not edit the email at all. I try to push the "edit message" butt...

Working offline, but no exchange server to go online with.
Hi, I use: Win XP SP2, Office 2003, SP 2. I started to create an Exchange Server account but shortly after, deleted it. Since then, I get an "Offline" icon in the right lower corner of the Also, when I try to use Rules and Alerts, I get the message: "You are currently working offline. To use this command, you must go online and connect to the Exchange Server." I do not have Exchange Server. There is no choice to go online in File Menu either. Any help would be appreciated. Create a new mail profile in Control Panel->Mail Icon->Profiles. = Configure it with...

autorecover doesn't work reliably during a forced reboot
Scenario: - Auto-recover is enabled in excel - An Unsaved Excel document is open - An SMS process for applying security or windows update patches causes a forced/unattended system reboot After the reboot is performed, the unsaved changes are lost in the excel document, because Autorecover doesn't think there is an autorecover document (apparently). I've had this happen a number of times, but it isn't 100% consistent. Is there anything I can do to increase the likelihood that autorecover will actually work (short of trying to disable SMS and windows update)? Is it possi...

how can i graph with log-log scales? #2
I wish to graph pricing curves using a logarithmic x-axis. Is this possible with Excel? If so, how do I do it? AirdromeTom - > I wish to graph pricing curves using a logarithmic x-axis. Is this > possible with Excel? If so, how do I do it? < Use an XY (Scatter) chart type. - Mike www.mikemiddleton.com ...

How can i get more 3D shapes for Auto shapes in excel?
Please suggest how i can get more shapes (simple as well as 3D ) in Auto Shapes of drawing toolbar in excel & word? ...

help with code on event call
Hi I am unsure how to code the following the table I am working on is Baan I am selecting the record using the orderNo field but depending if the field IcyDate empty or not I will open different forms the code I have in the event call at the moment is below and where I have put the ***** is where I need an if statement to see if the field IcyDate is empty or not Private Sub But_SelONo_Click() On Error GoTo Err_But_SelONo_Click Dim stDocName As String Dim stLinkCriteria As String Dim intcount As Integer intcount = DLookup("[Count-O-N]", "[Qu-cmm-countor...

Make combobox blank in macro
I use a wizard to create a combo box to find the record in my form. It creates macro automatcially. Works fine. After finding the record, I want to set the combo box to null or "" but need help add that line in macro. Thanks. Sorry but not many people use macros (me too) so can’t really be help there. But try this. I assume your macro is search on a name ? You have the ID field in the combo and the name and the ID field is the bound column of the combo ??? If so. 1st MAKE A BACKUP OF YOUR DATABASE Then – open you form in design view Right click the combo an...

e-mail just sits
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C5140F.E1605920 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am having a problem with my e-mail just staying in my outbox. I am = using Outlook that came with MS Office Professional 2003 I compose my mail and hit send and it just stays in my in box. It = doesn't matter how many times I hit send & receive it will no go out, = It happen about 50% of the time. It seams to happen more when I have an = attachments with the mail. --=20 Regards, Ron ...

How do I stop the code?
Hi I am using the following to duplicate certain fields in a new record which works fine. When I add the new record all the fields I have listed are copied. I am using the on Click event to trigger the code but how do I stop it? Every time I add a new record I get the same date in my fields. Const cQuote = """" Me.DriverFirstName.DefaultValue = cQuote & Me.DriverFirstName.Value & cQuote Me.DriverLastName.DefaultValue = cQuote & Me.DriverLastName.Value & cQuote Me.RegistrationNumber.DefaultValue = cQuote & Me.RegistrationNumber.Va...

Calculating days and dates
I need to calculate the number of working days (ie Monday to Friday) from the date entered in cell D4 (22/07/2005). This needs to update each day in cell E4. I can do this not based on working days, by entering =DATEDIF(D4,TODAY(),"d") but where in the formula do I reference it to working days or network days? HELP! -- HDV ------------------------------------------------------------------------ HDV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26299 View this thread: http://www.excelforum.com/showthread.php?threadid=395788 You can use the ...

Can we Take layaway payments from store #2
Hello, I have a customer that would like to make a payment on his layaway at out other location. Can this be done? and how? -- Reynold Cycle This is a multi-part message in MIME format. ------=_NextPart_000_04C2_01C70A46.94719A50 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Jose, Not without an add-on, which I've never seen to do this. --=20 Jeff=20 Check Point Software =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=...

how make outlook 2003 default and keep NG reader?
Hi, I have a perplexing problem concerning outlook express 6.0 and outlook 2003, both of which I have installed on a new laptop running xp home. I set up express first, and so it was the default email and newsgroup client. Subsequently Outlook 2003 was similarly configured. Here's the interesting part: If I set outlook 2003 as the default, the ability to go to the news reader goes away. If I set Express as the default, it comes back and I can get to the newsreader from either one. Any idea on how this can be resolved so the option to use outlook 2003 as the default newsgroup r...

There sure are a lot of posts from certain idiots today
it seems that the idiot is alive and being very stupid. ...

can't save files in subfolders using Word #2
I'm running Word on a Mac running OS X. When I go to save a file in Word, it only gives me the option to save in the root files like "Documents" or "Applications." How do I save the file into a subfolder? On a PC, I would double click to open the folders until I got to the folder I needed. That doesn't work here. Can someone help? I'm sure this is something obvious I'm missing. Thanks - Taylor In article <1163740041.258317.226140@b28g2000cwb.googlegroups.com>, taylorgriffin@gmail.com wrote: > I'm sure this is something obvious I'...

How can I sort and maintain formatting?
I have a very large excel doc (well over 1,000 rows, by 30 somethin columns) that require formatting (resizing the row height) each row b hand. I have done all of that tideous work, but now I need to sort b multiple columns, and when I do that, the formatting of the cells i not maintained. (The size of the row stays, rather than moves with th data) Please tell me there is a remedy and I haven't just wasted m time formatting for nothing! Thanks for any and all advice -- littlelostgr ----------------------------------------------------------------------- littlelostgrl's Profile: http:/...