Missing macro needed to sync (move 3 ranges) align with col-A

Hi there,

I hope if someone may be able to help out on constructing a missing macro, essentially for moving/re-aligning some rows (but only the SPECIFIED ranges) not entire row, that can go awry (misaligned) when new entrants joined the class, or some have left, which may occur every time workbook is opened.

The workbook LBI is opened on each class day (Mon - Fri), at least once, normally 1 - 4 times per day. There are 3 different subjects and each subject is taught by a teacher. The actual sheet has 200 rows but is currently not filled to that yet. Each day, each of the teachers opens the same workbook LBI.xlsx after their class has been conducted, & inputs values in ranges A2:A,F2:H for each kid. The next teacher who resumes class for the next subject on same day does the same, & so on till the 3rd teacher ends for the day.

The 2 key tasks of the workbook each day are to: I) see macro segment 2a: assign AL:AN to BL:BN to register for previous class day scores, but based on the criteria that dates in each row AG2:AG not blank and not = TODAY() (in AG1), else do next step till loop ends. I have been able to make this macro with the kind help of @davesexcel. II) segment 2b: assign latest F:H scores to the corresponding rows in AL:AN every single time workbook is opened regardless if each AG date equals TODAY (AG1) or not.

The target ranges with issue are: AF2:AG,AL2:AN,BL2:BN. All other cells are bound by other simpler macros so must not be moved.

A new class for this fall isto be opened consisting of 9 students, the first day of school, on Oct 30, 2020.


On the 1st day Oct. 30 trial class, all students register their scores, and there is no previous day scores data, so BL2:BN is blank.

On the official day of class, Nov. 2, the sheet does its task and first assigns the Friday scores to BL2:BN, and then assigns the latest scores for each subject during the day into AL2:AN. (see img)
Attachment 701107
Attachment 701111

Same day, at the end of the 3rd subject, all scores are assigned. (see img)
Attachment 701112

And, similarly for the next class day.


Basically, this macro seems to be handling well, that is if students stay at 9 kids and if nobody leaves school, and no new entrants join. Now, if new entrants join the class, say 3 new kids Billy, Edgar & Kylie joined class on Nov. 4, total students now makes 12. Then, A2:A,F2:H expands below an additional 3 rows, but the ranges AF2:AG,AL2:AN,BL2:BN remain stuck at 9 rows, with rows most being out of synced with A2:A,F2:H. (see img)
3a.JPG

This makes a need to plug in additionally another segment 1 in my macro, before the above 2a,2b segments, so that every time as the workbook opens, it has to first check for any misaligned issue, by checking to see if there is at least 1 pre-existing filled value in AF2:AF becoming NOT equal to A2:A (to make sure not a single is out of sync), & if all are checked to be perfectly aligned then assign A2:A to Af2:AF, and proceed to do segments 2a,2b.
Else, do not assign to AF2:AF, instead go look for & select only those misaligned rows in the ranges (AF2:AG,AL2:AN,BL2:BN) & sync them (either move values?/move cells?) by matching AF2:AF with A2:A & bringing along with them the corresponding values in AF2:AG,AL2:AN,BL2:BN accordingly. (see img)

5.jpg

After much thought, I think this is the way to resolve this out-of-sync situation. Can anyone help? here'smy current macro:

Please Login or Register  to view this content.

Stewart

hi maniacb


Appreciate your suggested code.

I'd like to ask, is there any way that a macro line could act in a similar way as the formula function INDEX MATCH, such that the old values in BL2:BN could search for the shifted ID names, and assign the prior values back to the new locations ?


If not, i will try to work & build on your code



cheers
Stewart

alright, i understand. i was experimenting with another way around the issue but have to create another workbook, then copy & paste the values there instead of inputing manually by each teacher.

The lame thing is i think my code below is very slow in popying & pasting over, it drags for a lengthy good 36 secs to complete. I wonder if you can suggest me a faster way to copy & paste just between 2 workbooks. I've searched online but most are too complicated or do not work when i adapt them to my code.
Here's the really slow macro i made today:

Please Login or Register  to view this content.

Stewart

so you mean, i have to stick back to my original & 34sec slow run code..? is there no other faster way ?

hi maniacb

tried that too, but unfortunately it's still the same, the same error msg and highlight is on the same line ..


searching for this error message on the net, i came across some similar situations of copy/paste between wbs examples that came up this error, i noticed most of the solutions involve the 3 common commands like ARRAY,UNION,RESIZE though i can't comprehend them. maybe you can..? do you think these could help the issue?

MannStewart

Here's my interpretation of your problem.
See if this works as you expected.

Please Login or Register  to view this content.

Edit
Replaced code and attachment due to a bug.

hi Jindon


thanks for the code, the AL2:AN part is correct working, but the BL2:BN part is wrong. reason is this: if criteria met, then BL2:BN has FIRST PRIORITY to receive the OLD values from AL2:AN BEFORE AL2:AN receives the new values from F2:H. can you tweak it that way? BL2:BN is to store old values (the previous class day scores). i tested your code, but BL2:BN is getting the SAME NEW values as AL2:AN is getting on running

also, can you help to add 1 more criteria to the date range AG2:AG, that is, if AG2:AG is blank but there is a new student on the row, then assign today date to the cell in AG2:AG


Stewart

hi Jindon

you see...

each time on opening workbook, FIRST AL2:AN needs to assign its values (called OLD values because it was previous data when saved wb) to BL2:BN, if criteria met, THEN after that done, itself AL2:AN receives assign from F2:H if criteria met (called NEW values because they are latest from F2:H). it's just like that. but if BL2:BN criteria to be populated on wb open is not met, then just assign F2:H to AL2:AN.

(you can visualise like this: upon wb open, the data in AL2:AN needs to migrate to BL2:BN first, BL2:BN holds old data for AL2:AN. then AL2:AN is empty and it gets new data from F2:FH. of course, both migrations need to be subject to criterias met)

let me know if the above is ok, or i'll resend you some screen shots basically is the same images attached in the post earlier ..

if it is a new student, ie. student's 1st day to class is today, then this student's BL2:BN will be blank, because he/she has no yesterday value, but if the student is at least 2 days or more already in this school, its BL2:BN on the row must have values.
But AL2:AN is necessarily always filled even if today is student 1st day. And, assign TODAY date to AG2:AG of the new student at the end of all assign processes, so that when open wb tomorrow morning, the date will be 1 day less than tomorrow, therefore purpose is to allow tomorrow AL2:AN to send to BL2:BN.

OLD student: AL2:AN assign to BL2:BN first (yesterday scores), where BL2:BN has (already filled yesterday minus 1 days ago scores) values, overwrite is ok. Then, F2:H assign to AL2:AN new values (today scores), assign TODAY() to AG2:AG
NEW student: F2:H assign to AL2:AN (existing is blank). Leave BL2:BN blank because AL2:An has no old values to offer. And, assign TODAY date to AG2:AG of the new student at the end of all assign processes, so that when open wb tomorrow morning, the date will be 1 day less than tomorrow, therefore purpose is to allow tomorrow AL2:AN to send to BL2:BN.


pls let me know if you need more clarif'n

hi Jindown

Here it is, the CORRECT Sequence Images:
it does explain what i was trying to say

So, let's say, all students are new, school opens brand new for students on its 1st day = Oct 30
Attachment 701498

Attachment 701499

Attachment 701500

Just before orange goes to AL2:AN, AL2:AN pre-existing values goes to BL2:BN (green) first, and date on AG2:AG is still previous date so criteria allows the green process to go ahead, after it is assigned, then the date becomes TODAY, so for the entire day no matter how many classes, the values in BL2:BN need not change anymore, it already holds previous day scores. it just waits till tomorrow to repeat this process:
Attachment 701501


End should be this
Attachment 701502

hi Jindon

pls disregard all my previous confusing images.

i've painstakingly constructed this new Entire storyline sample file, 14 sheets, in time sequence of a sampled 3 class days, which covers all main possibilities of the class operation.
kindly have a browse slowly, because i'm sure you will no longer say this confusing after you go through the sheets 1-by-1 carefully. Your code is in the button, but i think mainly what is needed now is the BL2:BN part. Anyway, pls go through this new attachment file sheet-by-sheet, i spent a long time in flu making it, it tells the whole picture and main possible circumstances that class will be in
NOTE: Please don't click to run the macro (if you run the macro in the file, the values that explain the Story will be altered, don't alter any values or you'll miss the entire explanation i was trying to make for you). This file is a Storyline Illustration file, its purpose is just for illustrating the sequence of what the school operation is every day. After you comprehend the story, look forward to see how the macro should be tweaked accurately

by the way, i opened your fFile: xlsm LBI_sample_test Lined up.xlsm, but i think you strayed further from the right understanding as that is not what the solution is.

Stewart

no , categ is there, do not ignore it

kindly browse carefully starting from Sheets admin1 to Sheets 3b in this sequence, pls observe the values , arrows & text narration


[Errata: school only closes for the day after teacher-c finishes updating the workbook and saves file]

Jindon,
i was not referring to the code, when i said sheets i meant sheet "admin1"is the first sheet, sheet "3b" is the final sheet in the Storyline.

Column "J" is very rarely changed, only set at the beginning of a new student enrolment, i'm not fully catching why you are focusing too much on this ..?
It is used as criteria, which i think you rightly wrote in your initial macro:

Please Login or Register  to view this content.

Please download the sample file again, is corrected for Illustration mistakes

where are you seeing this, maybe it was the draft i was working on trying to tweak your version to cover my criteria. i rechecked your code, i didn't put that into the above file this time

OK, i know to answer your question on that line, it was my original own attempt for the BROWN step. Every time before BROWN step can occur, i need this 3 criteria to be met, ie. date in AG is <> "", AG <>AG1 and the data in "J" categ is 1 of those specified.

ok, sure.

The conditions, or rather criteria for assigning are in the Story file, ie. for assigning BL2:BN, the conditions are in each brown box, & for assigning AG2:AG, AL2:AN, the conditions are in each blue box. I will outline them for you here again:

Do firstly, check conditions for assigning BL2:BN met or not [IMPORTANT: BL2:BN MUST be taken care first]:
if AND (AG2:AG <> "" , AG2:AG <> AG1, (OR (J2:J = "LOW", "NURSERY", "MID", "ONLINE")) then assign AL2:AN to BL2:BN {overwrite blank or filled}
[note: the data in AL2:AN at this point is NOT the latest data, ie. not TODAY's data, which is represented by F2:H. AL2:AN at this point contains YESTERDAY's data, and it is these old data that BL2:BN wants]

then after above, check conditions for assigning AG2:AG met or not:
if OR (AG2:AG <> "", AG2:AG = "", AG2:AG <> AG1) then assign AG1 to AG2:AG {overwrite blank or filled}

for AF2:AF, AL2:AN regardless (no condition) everytime at the final part of macro, assign F2:H to AL2:AN, A2:A to AF2:AF {overwrite blank or filled}


About the column AF , actually, i had created it just for the objective to sync the rows whenever they get out-of-sync. If with your new code, it automatically everytime syncs on macro run, this column has no more purpose and can be omitted

ignore displaying incorrectly see below
reply

yes, that was Exactly what i gave you in the really complete sample file yesterday in that Storyline file (LBI_sample_expl.xlsx) , telling from Before which is sheet "admin1", then on, to after, and after, and after till the end sheet "3b" ... in time sequence. Ajnyway, i will make again with the LBI_test files for you hang on

i think the before after sequence is kinda vague for you here, i wish you can see it clear. hang on, i'm goijng to try to make the files, hope i can make it simpler

Jindon

pls take another look at this most simplified before-after file, telling the main job of the macro for 1 teacher on running the macro. the macro's job is to make both before & after happen

i hope this would have cleared out the vaguesness if any you still have ..

both sheets shows macro already RUNNING what needs to happen WHEN the macro already RUNNING

i see it, that's what i need, but which code made this happen..?

how come i couldn't get this with any of all your provided codes

can you list out again the full code that you achieved this result ?

The one in the attached, so need to find out the reason why it doesn't work there.

Reason is, the most probably, the DATE recognition as I mentioned earlier.
Add one line in bold

Please Login or Register  to view this content.

When you run then code, it pauses in that line and get into debug mode.
After that, as you hit F8 key, the code executes one line.
when it reaches

Please Login or Register  to view this content.

Hover over the cursor to the variable and read the value for each a(i,2) & CLng(myDate)

i'm lost , so the code needs to be adjusted for the unwanted pause? the code in your attached file showing the correct result, was that adjusted?
should i apply that code, or which code should i apply now , or wait for your updated ..?

sorry, i coudn't understand at all what you meant by..
"Hover over the cursor to the variable and read the value for each a(i,2) & CLng(myDate) "

Jindon

in debug:

b(i, 2) = 29-Oct-2020
CLng(Date) = 44133

on first loop of F8

did that
the only difference this time is AG2:AG assigned AG1 date, but BL2:BN still blank ..

.. what am i missing or doing wrong ?

.. you mean debug & go to the line:
If CLng(CDate(b(i, 2))) <> CLng(myDate) Then
because you said change so the line now is above

i put STOP again at
Stop
For i = 1 To UBound(b, 1)

or put STOp at
a = Application.Index(.Value2, Evaluate("row(1:" & .Rows.Count & ")"), _
Array(1, 33, 6, 7, 8, 38, 39, 40, 64, 65, 66, 10))

which line i should put STOP?

Jindon

I want to analyse the OutPut code, because i think the problem BL2:BN is always blank is due to here:
so, i have 2 questions i need you to help explain for me, referring to attached image:

Output.PNG

ques-1: what is the task (denoted red in att img) of this line, because the code never gets pass this line to the lines below (i realised it never gets past this line via F8 debug)

ques-2: what do the numbers 9,10,11 (denoted yellow in the attached img) denote, are they column numbers? in my debug, i understand that this is for the BL2:BN, and it never executes the task. Also, i would think perhaps this section should run first in OutPut, like this, because it has to get the AL2:AN values before AL2:AN got assigned, i mean rearranged like this, don't you think:

Please Login or Register  to view this content.

I suspect the issue is, the relevant part for 9,10,11 is missing? i mean, maybe it should be iii? you defined iii as Long, but i can't find or see any algebra for part of iii
please comment


Stewart

Jindon

BL2:BN finally works! Great relief ...

.. but i discovered 1 problem now is with AL2:AN, ie. even when AG2:AG date = AG1, & the categ criteria is satisfied, the value in F2:H for that row does NOt send to AL2:AN

can you see which part of your code needs a tweak there..?
I think it is just add the condition: if students meet categ criteria, even though AG2:AG is not AG1, but ok to go ahead send values to AL2:AN (only for AL2:AN, do not touch BL2:BN that's perfect now as it is)

eg. att file, pls focus on FIFI and OPRAH, they are both "MID" and "NURSERY" students, meet criteria. Their AL:AN should change to 6,6,6 & 11,11,11 respectively. Now if you click button, this doesnt happen for these students.