98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » For Each Record, loop
For Each Record, loop
Resolved · 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.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Brighten up your Excel 2010 Spreadsheet by changing the colours of the gridlinesExcel 2010 allows you to change the colour of grid lines instead of keeping them in boring black. |