code not working with auto fill

I have a code working very well when I fill cells manually, but when I use 
auto fill the code stops working as if it does not exist . how  can I  make 
the code work with the auto fill.  
Thank you.


0
forcast (6)
3/24/2008 5:16:00 AM
excel 39879 articles. 2 followers. Follow

4 Replies
609 Views

Similar Articles

[PageSpeed] 22

Don't really understand the question. Do you mean that you are recording code 
but the recorded code does not work with Autofill?

-- 
Regards,

OssieMac


"forcast" wrote:

> I have a code working very well when I fill cells manually, but when I use 
> auto fill the code stops working as if it does not exist . how  can I  make 
> the code work with the auto fill.  
> Thank you.
> 
> 
0
OssieMac (238)
3/24/2008 6:51:01 AM
Dear OssieMac
 Thank you for replying my problem is :
This the code
If Target.Column = 3 Then
    If Selection.Count > 1 Then Exit Sub
    Target.Offset(0, -1).Formula = "=Row()-1"
    If Target = "" Then Target.Offset(0, -1) = ""
End If
End Sub
Now if you fill C1, C2 manually with number 1 ,2 you can see the result on 
B1,B2 it is 0,1 now everything is OK so far but if you auto fill 
C3,C4,C5……..you can not see any result on B3,B4,B5……….also if you make C7 = 
F4 and you fill F4 with any number say 10 you can see your number 10 
automatically on C7 but no result on B7 which mean the code is not respond .
Thank you    
forcast

"OssieMac" wrote:

> Don't really understand the question. Do you mean that you are recording code 
> but the recorded code does not work with Autofill?
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "forcast" wrote:
> 
> > I have a code working very well when I fill cells manually, but when I use 
> > auto fill the code stops working as if it does not exist . how  can I  make 
> > the code work with the auto fill.  
> > Thank you.
> > 
> > 
0
forcast (6)
3/24/2008 7:37:00 AM
Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRng As Range
    Dim myCell As Range
    
    Set myRng = Intersect(Target, Me.Range("C:C"))
    
    If myRng Is Nothing Then
        Exit Sub
    End If
    
    For Each myCell In myRng.Cells
        If myCell.Value = "" Then
            myCell.Offset(0, -1).Value = ""
        Else
            myCell.Offset(0, -1).Formula = "=Row()-1"
        End If
    Next myCell
End Sub

forcast wrote:
> 
> Dear OssieMac
>  Thank you for replying my problem is :
> This the code
> If Target.Column = 3 Then
>     If Selection.Count > 1 Then Exit Sub
>     Target.Offset(0, -1).Formula = "=Row()-1"
>     If Target = "" Then Target.Offset(0, -1) = ""
> End If
> End Sub
> Now if you fill C1, C2 manually with number 1 ,2 you can see the result on
> B1,B2 it is 0,1 now everything is OK so far but if you auto fill
> C3,C4,C5……..you can not see any result on B3,B4,B5……….also if you make C7 =
> F4 and you fill F4 with any number say 10 you can see your number 10
> automatically on C7 but no result on B7 which mean the code is not respond .
> Thank you
> forcast
> 
> "OssieMac" wrote:
> 
> > Don't really understand the question. Do you mean that you are recording code
> > but the recorded code does not work with Autofill?
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "forcast" wrote:
> >
> > > I have a code working very well when I fill cells manually, but when I use
> > > auto fill the code stops working as if it does not exist . how  can I  make
> > > the code work with the auto fill.
> > > Thank you.
> > >
> > >

-- 

Dave Peterson
0
petersod (12004)
3/24/2008 12:23:09 PM
Dear Dave Peterson
Thank you very much It is excellent solution .

Regards
Forcast  


"Dave Peterson" wrote:

> Maybe...
> 
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     Dim myRng As Range
>     Dim myCell As Range
>     
>     Set myRng = Intersect(Target, Me.Range("C:C"))
>     
>     If myRng Is Nothing Then
>         Exit Sub
>     End If
>     
>     For Each myCell In myRng.Cells
>         If myCell.Value = "" Then
>             myCell.Offset(0, -1).Value = ""
>         Else
>             myCell.Offset(0, -1).Formula = "=Row()-1"
>         End If
>     Next myCell
> End Sub
> 
> forcast wrote:
> > 
> > Dear OssieMac
> >  Thank you for replying my problem is :
> > This the code
> > If Target.Column = 3 Then
> >     If Selection.Count > 1 Then Exit Sub
> >     Target.Offset(0, -1).Formula = "=Row()-1"
> >     If Target = "" Then Target.Offset(0, -1) = ""
> > End If
> > End Sub
> > Now if you fill C1, C2 manually with number 1 ,2 you can see the result on
> > B1,B2 it is 0,1 now everything is OK so far but if you auto fill
> > C3,C4,C5……..you can not see any result on B3,B4,B5……….also if you make C7 =
> > F4 and you fill F4 with any number say 10 you can see your number 10
> > automatically on C7 but no result on B7 which mean the code is not respond .
> > Thank you
> > forcast
> > 
> > "OssieMac" wrote:
> > 
> > > Don't really understand the question. Do you mean that you are recording code
> > > but the recorded code does not work with Autofill?
> > >
> > > --
> > > Regards,
> > >
> > > OssieMac
> > >
> > >
> > > "forcast" wrote:
> > >
> > > > I have a code working very well when I fill cells manually, but when I use
> > > > auto fill the code stops working as if it does not exist . how  can I  make
> > > > the code work with the auto fill.
> > > > Thank you.
> > > >
> > > >
> 
> -- 
> 
> Dave Peterson
> 
0
forcast (6)
3/24/2008 2:00:01 PM
Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

OL2002 : "header only" option works only once !!!!!
Hi, I have an IMAP account and OL2002 as client. I have check for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit, download item description only). It works only the first time i start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Windows live messenger has stopped working
when I boot my PC and start Windows 7 WLM during the startup phase stops working before it has fully started. If however i start it after the boot up process is complete it works fine. I am using Window 7 64 Home Prem... and am running a new ATI 5800 series card, have 4g of ram etc..... Any ideas? I have read through the posts on this board and i do not see my problem though it is likely related to the other ones i have seen. Greetings, It might be a bit hard to isolate what's causing this without disabling other things starting at startup, as it's probably just a...

Turn off save work reminder
How can I stop being reminded to save my work in Publisher 2000? Thanks in advance, Josh Options, User Assistance tab, clear "Remind to save publication." -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "josh page" <josqb@cinci.rr.com> wrote in message news:46b0a69f$0$8926$4c368faf@roadrunner.com... > How can I stop being reminded to save my work in Publisher 2000? > > Thanks in advance, > Josh > ...

Auto adjust dates in a chart
I would like to build a line chart that automatically plots values for the past 14 days, no matter when I open the chart. I have set up a column with now() in the first cell and now()-1, now()-2, etc. in a series for the following 13 cells down to now()-14. This column becomes the data range for the x-axis. If I test it by changing the system date, the now() values do not change automatically. I know there must be a way to do that. But the further concern is how to link values in another column with the proper now()-x value to display what I want to see. I want to use this chart to...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

configure outlook to work with AOL
Hello...help How do I configure outlook to work with my AOL? Milt <anonymous@discussions.microsoft.com> wrote: > Hello...help > How do I configure outlook to work with my AOL? You don't. See <http://members.aol.com/adamkb/aol/mailfaq/#3rd-party>. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finger/Web for PGP & S/MIME adamkb@aol.com | http://www.lull.org/adam/ ...

options/tasks OUTLOOK 2007 not working
Example, insert picture option cloeded as if trial period over unable to use many in all office 2007 programs. Was fine for a couple days and then this. Test driving new 2007 version for my office - pretty sure I may have done something customizing. JAS Are you using HTML or RTF message format? Some of the options are only available in specific formats. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a ...

Dialoog Box Close Buttons Won't Work
Hi: I have a vc++ 6.0 program that I recently converted to an ActiveX control so that I could use it as a part of another program. The program that was converted to an ActiveX control has a number of dialog boxes that pop up to perform various functions. In the original program the dialog boxes work fine, but in the ActiveX control the dialog box won't close when the "Close" button in the upper right corner is pressed. The program gets hung up whenever this button is pressed and I need to do a "ctrl-alt-delete" and an "End Task" to close the program. Ca...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...

Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...

Auto populate field in table
I have an Access data base that has acumulated 7 years of HR info. We plan to moving this info to HR Application. The new application has a field called [EmpStat}. On my access data base I do not have a [EMPStat] field but I do have a [Termation date]. Because we have a lot of employees that have terminated it would be great to import into the HR program the word "Terminated" into [EMPStat]. How can I do the following in a table: create a new column and call it [empstat] then say If [Termination Date] Is Not Null put "Terminated" in the [empstat] field. ...

Offline rules don't work PLZ!
My offline rules in 2003 don't work. I tried recreating and all trivial staff. I see many users posting the same Q with one thing in common: number of posts = 1 (i.e - no answers). I hardly beleive such a basic bug would not be resolved by MS. Can anybody help please? Dan Dan, I posted that problem, too. No answers. My qorkaround is to use the cached mode. Since you are always online in that, it seems to work. -- Thomas Borchert, Mon, 23 Feb 2004 17:44 +0100 ...

problem working with hardware interrupt
Hi, I have searched google and have not found any indicator of an answer to the following question. I have an application that needs to synchronise to a hardware interrupt. I have a hardware driver that works fine at kernel level, and has a connection to the user level by recognition of the interrupt event occurrence. This occurs in an example dialog based mfc program, in a global function declared outside the class constructor. It works by updating an interrupt counter and updating two global variables. I want to have this interrupt event recognised by teh main GUI thread, but anytime ...

help with/for pivottable code
Hi everyone! I have code that takes a large workbook and divides it into a number of separate new workbooks based on data that is in column A. What i need is to create code that will create a pivot table in the new workbook. here is my code: Sub Regionalize() Dim wks As Worksheet Dim wksNew As Worksheet Dim wbk As Workbook Dim rng As Range Dim cell As Range Dim lRow As Long Dim sFileName As String Dim sFolder As String Dim sRegion As String Set wks = Sheets("region") Set rng = wks.Range("regiondata") ...