+ Reply to Thread
Results 1 to 5 of 5

For Each Record, Check If... Then Loop..

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    6

    For Each Record, Check If... Then Loop..

    Hello!

    I am hoping one of you may be able to help me..

    i have lots of data in different sheets, and i basically need to do the below process:

    I beleive you can use ~"for each" record.. then loop through etc..

    If value in sheet (GB).range B:B, is equal to sheet (alll Cases).range(B:B) value...
    Then see if GB.range (O:O), is equal to (All Cases) range(H:H) on the same row.

    If they are both equal, then copy sheet(All Cases).range(I:R) to that row in GB where they match.

    If they are not equal, then move to next record and loop through all the way.

    For Example...

    If Sheets(GB).Range(B2).value = Sheets(All Cases).Range(B34).value then
    Check if Sheets(GB).Range(O2).value = Sheets(All Cases).range(H34)

    If both contain equal value, then
    All Cases.Range(I34:R34).Copy
    and paste into GB.Range(Q2:Z2)

    I am hoping to get this in a loop to check every value in GB(B:B) against ALL Cases(B:B), if the value in GB(B2) doesn't appear in All Cases(B:B) then just move onto next value..

    I hope that makes sense, I apologise for the mixed style of the above, but i am trying to make it more clear!

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: For Each Record, Check If... Then Loop..

    Please attach a sample excel file.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    12-03-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: For Each Record, Check If... Then Loop..

    Is it possible to get code for the following without posting a sample..

    Where PRN, VIN, NSC are Fields in Sheets(GB)
    Where "*ac" is equivalent field in Sheets("All Cases")

    'For Each Record in BG("A:A")
    'if PRN = PRNac then
    'if VIN = VINac then
    'if NSC = NSCac then
    'Sheets(All Cases).select
    'Columns("I:R").Copypaste
    copypaste to range("Q:Z")

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: For Each Record, Check If... Then Loop..

    Please do not create duplicate threads. Its against the forum rules.

    If a particular record is in BG - row 2 - with value 123, will the same value 123 be present in All Cases row 2?

  5. #5
    Registered User
    Join Date
    12-03-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: For Each Record, Check If... Then Loop..

    Apologies, I had lost where my first thread was. and felt the new style of asking was better.

    It might be in there, that is what i want to check for. Basically things get modified in All cases, and each other sheet get updated daily, so what i need to do. is check if any PRN and in both, if so then check VIN, if they are the same, then i need to check if NSC is the same, if all these are equal on sheet(GB) and also Sheet(All Cases) then i need to copy rows colum I:G for that particular row into the GB where that matched PRN is.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1