for each record loop

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » For Each Record, loop

For Each Record, loop

resolvedResolved · High Priority · Version 2007

Jordan has attended:
Access VBA course

For Each Record, loop

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!

RE: For Each Record, loop

Hi Jordan

Good to hear from you. Your code is getting pretty close to the real thing but there are a number of changes you will have to make. Your code should store the current row on Sheet GB in a variable, and the current row on Sheet All Cases in a separate variable, as they are dissimilar.

A For Next loop would work, as would a Do Until loop (which might be simpler to code).

For the copy and paste code, it's easiest to record a macro of yourself copying and pasting.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Thu 20 Dec 2012: Automatically marked as resolved.


 

Excel tip:

Brighten up your Excel 2010 Spreadsheet by changing the colours of the gridlines

Excel 2010 allows you to change the colour of grid lines instead of keeping them in boring black.

Select the File tab on the Ribbon, click Options, click Advanced, scroll down to ''Display options for this worksheet.'' Next to ''Gridline colour,'' choose your favourite colour, then once you've done this, click OK. Easy!

View all Excel hints and tips


Server loaded in 0.09 secs.