Anybody Help with previous question

Hi,
I'm getting desperate to solve this, so my apologies for posting this again, 
but can anybody offer any help with this previous question. If the link 
doesn't work I have copied the orig question below.

http://www.microsoft.com/office/community/en-us/default.mspx?pg=4&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&fltr=&mid=4044fba5-9f57-4be7-85a7-92ebb9093772


I  have (been given) the code below that will add a row of 'jobs' to a 
worksheet named 'Database'. There isa check to see if these jobs have been 
already added and a response advising if so.
Is there a way to change the code so that if jobs 1-10 are added, then the 
user creates jobs 11-20 he/she can add these also without any problems. At 
the moment the code is written so that if any 'jobs' are already found in the 
'database' the 'add' function is rejected. 
example,
through the morning jobs are entered into the log, lets say jobs 1-10. These 
jobs are then added to the database by the user. In the afternoon another 
user has created jobs 11-24 and he too adds these to the database.
I want the code to check and see which jobs (if any) have already been added 
to the database and then add any outstanding ones.

Hope that is clear and go easy, as I'm quite new to this.

here is the (donated) code.......

Sub add_Anydays_jobs()

Dim DataWks As Worksheet
Dim LogWks As Worksheet
Dim FoundACellDiff As Boolean
Dim FoundAGroupMatch As Boolean
Dim RngToCopy As Range
Dim testRng As Range
Dim iRow As Long
Dim FirstRowToCheck As Long
Dim LastRowToCheck As Long
Dim cCol As Long
Dim cRow As Long
Dim DestCell As Range

Set DataWks = Worksheets(ActiveSheet.Name)
Set LogWks = Worksheets("Log")
Set RngToCopy = DataWks.Range("a8:n34")

With LogWks
    FirstRowToCheck = 5 'headers?
    LastRowToCheck = .Cells(.Rows.Count, "A").End(xlUp).Row
    FoundAGroupMatch = False
    For iRow = FirstRowToCheck To LastRowToCheck
    'topleftcell of possible range to paste
        Set testRng = .Cells(iRow, "A")
        FoundACellDiff = False
        For cRow = 1 To RngToCopy.Rows.Count
            For cCol = 1 To RngToCopy.Columns.Count
                If CStr(RngToCopy.Cells(cRow, cCol).Value) _
                = CStr(testRng.Cells(cRow, cCol).Value) Then
                'still the same
                'so do nothing
                Else
                    If CStr(RngToCopy.Cells(cRow, 2).Value) <> "" Then
                        FoundACellDiff = True
                    End If
                    Exit For
                End If
            Next cCol
            If FoundACellDiff Then
            Exit For
            End If
        Next cRow
        If FoundACellDiff = False Then
        FoundAGroupMatch = True
        Exit For
        End If
    Next iRow
    
    If FoundAGroupMatch = True Then
        MsgBox "This log has already been copied to the database", 
vbExclamation
        'exit sub '????
    Else
        'do the copy
        Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
        DestCell.Resize(RngToCopy.Rows.Count, _
        RngToCopy.Columns.Count).Value _
        = RngToCopy.Value
        MsgBox "All Today's Jobs Added Successfully !", vbInformation
    End If
End With


End Sub



Thanks
0
Anthony2219 (255)
7/26/2005 8:31:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
419 Views

Similar Articles

[PageSpeed] 59

If each job is a single row, you could just loop through the rows looking for a
match (in the job id?).

I don't think I'd start with this code as a base.

Maybe something like this...

Option Explicit
Sub testme()
    
    Dim myKeyCell As Range
    Dim myRng As Range
    Dim myLookUpRng As Range
    Dim DataWks As Worksheet
    Dim LogWks As Worksheet
    Dim res As Variant
    Dim destCell As Range
    
    Set DataWks = ActiveSheet
    Set LogWks = Worksheets("Log")
    
    With DataWks
        Set myRng = .Range("a2:a10") 'whatever it is?
    End With
    
    With LogWks
        Set myLookUpRng = .Range("a:a")
    End With
    
    For Each myKeyCell In myRng.Cells
        res = Application.Match(myKeyCell.Value, myLookUpRng, 0)
        If IsError(res) Then
            'not there
            With LogWks
                Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End With
            myKeyCell.EntireRow.Copy _
                Destination:=destCell
        Else
            'key is there
            'what happens here
        End If
    Next myKeyCell
        
End Sub

(untested, but it compiled ok)

Anthony wrote:
> 
> Hi,
> I'm getting desperate to solve this, so my apologies for posting this again,
> but can anybody offer any help with this previous question. If the link
> doesn't work I have copied the orig question below.
> 
> http://www.microsoft.com/office/community/en-us/default.mspx?pg=4&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&fltr=&mid=4044fba5-9f57-4be7-85a7-92ebb9093772
> 
> I  have (been given) the code below that will add a row of 'jobs' to a
> worksheet named 'Database'. There isa check to see if these jobs have been
> already added and a response advising if so.
> Is there a way to change the code so that if jobs 1-10 are added, then the
> user creates jobs 11-20 he/she can add these also without any problems. At
> the moment the code is written so that if any 'jobs' are already found in the
> 'database' the 'add' function is rejected.
> example,
> through the morning jobs are entered into the log, lets say jobs 1-10. These
> jobs are then added to the database by the user. In the afternoon another
> user has created jobs 11-24 and he too adds these to the database.
> I want the code to check and see which jobs (if any) have already been added
> to the database and then add any outstanding ones.
> 
> Hope that is clear and go easy, as I'm quite new to this.
> 
> here is the (donated) code.......
> 
> Sub add_Anydays_jobs()
> 
> Dim DataWks As Worksheet
> Dim LogWks As Worksheet
> Dim FoundACellDiff As Boolean
> Dim FoundAGroupMatch As Boolean
> Dim RngToCopy As Range
> Dim testRng As Range
> Dim iRow As Long
> Dim FirstRowToCheck As Long
> Dim LastRowToCheck As Long
> Dim cCol As Long
> Dim cRow As Long
> Dim DestCell As Range
> 
> Set DataWks = Worksheets(ActiveSheet.Name)
> Set LogWks = Worksheets("Log")
> Set RngToCopy = DataWks.Range("a8:n34")
> 
> With LogWks
>     FirstRowToCheck = 5 'headers?
>     LastRowToCheck = .Cells(.Rows.Count, "A").End(xlUp).Row
>     FoundAGroupMatch = False
>     For iRow = FirstRowToCheck To LastRowToCheck
>     'topleftcell of possible range to paste
>         Set testRng = .Cells(iRow, "A")
>         FoundACellDiff = False
>         For cRow = 1 To RngToCopy.Rows.Count
>             For cCol = 1 To RngToCopy.Columns.Count
>                 If CStr(RngToCopy.Cells(cRow, cCol).Value) _
>                 = CStr(testRng.Cells(cRow, cCol).Value) Then
>                 'still the same
>                 'so do nothing
>                 Else
>                     If CStr(RngToCopy.Cells(cRow, 2).Value) <> "" Then
>                         FoundACellDiff = True
>                     End If
>                     Exit For
>                 End If
>             Next cCol
>             If FoundACellDiff Then
>             Exit For
>             End If
>         Next cRow
>         If FoundACellDiff = False Then
>         FoundAGroupMatch = True
>         Exit For
>         End If
>     Next iRow
> 
>     If FoundAGroupMatch = True Then
>         MsgBox "This log has already been copied to the database",
> vbExclamation
>         'exit sub '????
>     Else
>         'do the copy
>         Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
>         DestCell.Resize(RngToCopy.Rows.Count, _
>         RngToCopy.Columns.Count).Value _
>         = RngToCopy.Value
>         MsgBox "All Today's Jobs Added Successfully !", vbInformation
>     End If
> End With
> 
> End Sub
> 
> Thanks

-- 

Dave Peterson
0
petersod (12004)
7/26/2005 12:26:29 PM
Reply:

Similar Artilces:

Does anybody know of any free templates for managing transformatio
Does anybody know of any templates for business or process transformation to work with Project, Visio or Powerpoint? Looking at transformation is a journey without end (but with demonstrable changes/milestones/improvements along the way and is about culture and attitude more than technology or equipment. Accepts that perhaps many concurrent activities covering differnt core capabilities and lines of development occur in parallel, sometimes with the same actors, sometimes different and interacting. At some point take stock, refresh reenergise and move forward. ...

Money 2004 Password: Breaking, Circumventing, Help Around?
My sister passed away unexpectedly, leaving all her financial records in Microsoft Money, and no password in evidence. I have tried guessing, but she was pretty crafty and would be as likely to have "ooga-booga 398" as a password, so that's a deadend. I MUST get in to do her taxes, manage personal loans, checkbook, etc. The Money help says to use the secret question, but there's no prompt for a secret question. If they want me to go through Passport - I don't know of any MSN, Hotmail, or such email account - so that would just be another guessing game. I'...

Please Help
Hi all, I have been given a task to find out information for a list of part numbers. The information can be found in another Excel table that I have, which is quite large (~25,000 rows). Since I'm working with a large number of part numbers, I'm trying to extract the information from the larger table so that I can use it to complete the new "smaller" table (~900 rows). I've tried to add the part numbers from the small table into the existing larger table, and then resort, however I'm running into problems. For some reason the part numbers are not sorting corre...

Help with decling balance formula
I am trying to setup a worksheet with this scenrioas an example: Opening Balance off $300 in Columun O6 First deduction of $30.00 in Columun N7 I want to show a declining balance as additional deductions are entered in Columun N Thank you in advance for your support. HankL Hi HankL, If the deductions in Column N are +ve, try: =O6-SUM(N7:OFFSET(N7,COUNT(N:N)-1,)) If the deductions in Column N are -ve, try: =O6+SUM(N7:OFFSET(N7,COUNT(N:N)-1,)) If you know the last possible cell in Column N that will be used for the deductions, replace 'OFFSET(N7,COUNT(N:N)-1,)' with that cell ...

Outlook to Outlook Express. Help!
I'm really at my wits end. When I left my old job they burned a cd for me of all my Outlook messages, contacts etc. but when I tried to load the .pst files on to my laptop at home I quickly found it I wasn't able to as I only have Outlook Express. I now live in another country and not able to ask my old IT dept for help. Does anyone know what can be done now? I've posted this question on a chat site before and the responses I receive always related to transferring OE files to Outlook. I am trying to go the other way i.e. converting Outlook files to be read on to Outlook ...

need help with KB article Q315407.
I need to use this registry tweak to optimize our memory usage on Exch2003 on Server2003. We now have 2 GB memory and the system is rebooting or blue-screening after 8 hours or so. The problem is that the article says to click Edit > Add Value in the Registry Editor, but the only options available are Edit > New > String Value, Binary Value, Word Value, etc.. Which Value do I create ? ? Thanks. dword "chrism" <chris@no_spam> wrote in message news:O$3C$IVhEHA.904@TK2MSFTNGP09.phx.gbl... >I need to use this registry tweak to optimize our memory usage on Ex...

Strange message in Windows XP Help & Support
Please see the Outlook Express header which follows my signature. My query is that when you open the message instead of seeing the message in the message source ( not included below ) you see the last Outlook Express message you opened. I am wondering whether this is malware or some corruption in Outlook Express. As it only occurs with this particular newsgroup message I suspect malware. I do not generally get malware. Two infections in 12 years. I have not read of a similar experience. Has anyone else encountered it? TIA -- Gerry ~~~~ FCA Stourport, England E...

Spam Question #2
Hi, This is a question about some messages I am getting from my server. It is a general SMTP and spam question. My boss keeps telling me - I never sent this message, but here's a message telling me it couldn't be delivered! I am wondering if these messages are actually being sent through our server, or somehow spoofed. Here's what makes me think they may be spoofed: Return-path: <here@us.com> Received: from [59.93.199.74] (helo=ilisa.com) by host28.ipowerweb.com with esmtp (Exim 4.43) id 1Ee7h6-0004U2-K6 for mpratap@progia.com; Mon, 21 Nov 2005 01:10:59 -0800 From: ...

Help With Macro
I have data all structured in the same way (same column headings) across multiple sheets within the same workbook. Is there an easy way to concatenate all of the data, without any empty rows, into one summary sheet in the workbook? Thanks in advance. Kevin What do you mean by concatenate in this context? Do you want the summary sheet to contain all of the not-empty rows of data from all of the other sheets? Or are you somehow wanting some kind of actual summary of the data on the others? If you do want all data from all other sheets, then the code below should help. ...

More CChildFrame questions
I have my own pointer to a view: ViewResourceLoadChart *m_viewResourceLoadingChart; I create in my code. the view is attached to a CChildFrame, but when I call close on a childframe (pChild->SendMessage(WM_CLOSE);), it destroys the view as well. How can I retain my view for future use? On Jun 21, 9:12 am, "GT" <ContactGT_remo...@hotmail.com> wrote: > I have my own pointer to a view: > ViewResourceLoadChart *m_viewResourceLoadingChart; > > I create in my code. the view is attached to a CChildFrame, but when I call > close on a childframe (pChild->Send...

date issues. HELP!!!
I update the same spreadsheets everyday. The company I work for works 24 hours a day so my spreadsheet are titled with yesterday's date through today. (ex. Oct 15- Oct 16) I know that if I hit CTRL ; it will automatically fill that cell with today's date, but can it be setup to display yesterday through today? Everyday I must go on each sheet and change this area before I press on. I would love to have it so I could hit a shortcut for this. Thanks. Hi, Try this =TEXT(TODAY()-1,"mmm d")&" - "&TEXT(TODAY(),"mmm d") -- Thanks, Shane Devensh...

Help with IF
I've used the following to calculate a multiplied unit cost per kilo with a minimum charge of 194. =IF(B2<100,194,B2*1.94) Now I want to allow a lower charge of 1.84 for multiples over 999 in the same cell. I've tried (guessed!!) the following but to no avail. =IF({b2<100,194,b2*1.94},{b2>999,b2*1.84}) Would appreciate any suggestions -- kinsey This assumes that 1000 would be 1840 =IF(B2<100,194,IF(B2>999,B2*1.84,B2*1.94)) as against 999 @ 1.94 and the rest at 1.84 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my ...

BSOD ERROR HELP!!!
Hi, I'm new with this but can someone help me with these BSOD errors? I reformated my computer lately cause I had computer problems and my computer keeps restarting every like 10 mins. I try to use firefox or internet explorer it closes itself every 7 mins. The BSOD first error stop:0x0000008E (0xc0000005, 0xF773D926, 0xA5BE2A40, 0x00000000) sr.sys address F773D926 base at F7736000 second error is 0x0000007E (0xC000001D, 0xF7cBABB4, 0xF7CBA880) Computer- asus p4c800 On Aug 14, 8:31=A0am, david <da...@discussions.microsoft.com> wrote: > Hi, I'm new with this but can s...

Nillable Attribute Help Please
Greetings, I have a bit of XML I am looking at that puzzles me. <s:element [max occurs, minoccurs info here] name="MyElement" nillable="true" This is where I am puzzled. I looked up "nillable" but only got info on "nil" Nil states that if set to true that the value can't have content in it. If false the value must have content. This guy was generated WSDL that refers to a function that passes a parameter, where "MyElement" is the name of the parameter. It is not optional and requires a value (it is a string if that helps)...

Formula help #30
Just recently som sort of issue is going on in excel for me. I can create formulas no problem, but when I go to copy and paste it to another cell it captures the formula, but returns the exact same value as the cell copied even when the result should be totally different. The only way I can get the new cell to rteturn the real value, woudl be to either save the file or to double-click in the cell and reset the formula. When working with a large number of copied and pasted cells, this doesnt work well. If I save, I cant undo my last action, and its not scalable to double click and res...

Help!!!
As the last part of the process of taking our network to native mode before starting the upgrade process from Exchange Server 5.5 to Exchange Server 2000, I applied Service Pack 4 to all my servers. (Something I needed to do on the one day that the critical servers were not needed since we were dark.) No problems with any of the other servers, but when the Exchange Server restarted, it failed with a corrupted Exchange directory. Looking at the event logs, it appears as if the system tried to do a reindex of a secondary pointer index as a result of the upgrade (informational event 174). It ...

Help me!
I have some trouble with vc++ when I was using vc++ calculate some data p q r t are double a is float but when calculating the result is like this r=1.#INF00 what is the 1.#INF00 means and why ? thanks p=0.000018,q=0.000120,r=0.005419,t=0.000000 a=-30.000000,k=0.005488 p=0.000020,q=0.000134,r=0.006004,t=0.000000 a=-20.000000,k=0.006081 p=0.000022,q=0.000154,r=0.006686,t=0.000000 a=-10.000000,k=0.006774 p=0.000024,q=-0.000066,r=1.#INF00,t=0.000000 a=0.000000,k=1.#INF00 p=0.000026,q=-0.000046,r=1.#INF00,t=0.000000 a=10.000000,k=1.#INF00 p=0.000028,q=-0.000030,r=1.#INF00,t=0.000000 a=20.0000...

autofilter help #2
when i set my auto filter it stops at blank lines rather than allowing me to select "blanks or non blanks" the data that it filters doesnt pass the blank line so i have dispatched arrived arrived dispatched when i set the auto filter to show "arrived" it still shows the second "dispatched" because it is after the blank line is there some setting causing this Hi Max, If you have only selected the first cell in a column or columns then applied Autofilter it will only select as far as the first blank it comes to. If you want it to select all of your data ...

Help with guidelines
I cannot find an answer to a question I have. In Pagemaker, column guides and ruler guides can, in fact generally do, appear on top of the document. In Publisher, I can only see them behind the paper at the top/bottom of the page. This makes them relatively useless. What am I doing wrong? Thanks in advance for your help. dan Just make your Text Frames transparent - Ctrl+T -- After managing to set up OE-QuoteFix on his new PC, Ed reads a message from ���MS�Publisher��� <donotknock@nobodyhome.com>... > Just make your Text Frames transparent - Ctrl+T And when you'r...

forgot password?please help
I FORGOT MY PASSWORD ON MY MICROSOFT MONEY 2000 BUSINESS - PERSONAL I TRYED EVERY NAME AND NUMBER FOR 2 DAYS tsger@aol.com wrote: > I FORGOT MY PASSWORD ON MY MICROSOFT MONEY 2000 BUSINESS - > PERSONAL I TRYED EVERY NAME AND NUMBER FOR 2 DAYS That's got to be VERY FRUSTRATING! Not only that, but your CAPS LOCK KEY is stuck! I'm sorry for you! ...

ACH Check Conversion at POS anybody doing this?
I am currently setting up Check verification or maybe we will use ACH check conversion. I would appreciate any advice or warnings from users who have tried and failed or tried and tried and tried and finally suceeded. We have been trying to get Positive and Abanco and Geti to get echeck verification running for 2 months still no luck. -- RMS 1.3, MSDE 3 Lanes, 5 Back Office Clients XP sp2 Server 2003 sp1 Domain Static IP ...

Anybody had this problem??
Hi, Having a serious issue with Publisher 2002 and my HP Deskjet 5550. There has been no problems until I started pasting in some HI-Res pics from a mfgr. The pictures are CMYK. I guess normal pics are usually RGB. Anyway if I stick just one picture that is a CMYK color format into my document Publisher crashes when I give it the print command. I have to change the picture in Corel Photo-Paint to RGB. Then and only then will the page print out. Anybody know anything about this? Thanks if you do and take the time to tell me Catt J Send one of the pics to me. I have Pub 2002 and I have ...

Urgent help needed.
I just migrated from 5.5 to 2003. I was able to send and recieve mail from internal recepiants. I also was able to recieve mail from @deltaprocess.com addresses. I was not able to recieve mail from @deltaenvironmental.com addresses. I looked at my IIS and noticed that there were no SMTP virtual servers. I checked under add/remove programs and it showed them as being installed. I then removed ASP.net and IIS. next i reinstalled each of these. Now I can not send or recieve mail at all. The mail leaves the outbox but dosn't get delivered. What can I do? Please help. You need to re-...

Help needed re: parameter query
I create a parameter query to allow users to check records with interview dates that fall between a specific time window on a timely basis. To do so, I first create 2 fields as: start_date: [On! or AFTER (m/d/yyyy):] end_date: [On or BEFORE (m/d/yyyy):] Then for the "interview date" field, I put in the criteria: >=[On or AFTER (m/d/yyyy):] And <=[On or BEFORE (m/d/yyyy):] This way when opening/running the query, the user will be prompted to enter a start date and an end date. However, the test run picks up one record that falls outside the range (along with all other r...

HELP: Where did they go Outlook 2002
When I get an email with photos inserted (not attached) I tend to want to forward them to family and friends. When I select FORWARD, I get a new email with the photos visible. I then type addresses, click send, my address book pops up to properly select the complete addresses and POOF, all the pictures disappear???? Help! JDS is this one unsolvable? "JDS" <noway@notnow.net> wrote in message news:1114430017.e385121bb316099da294f8d93a9109c9@teranews... > When I get an email with photos inserted (not attached) I tend to want to > forward them to family and friends....