Scoring Simplified

will look at it now
gowflow

thanks but sir..... did you under stand what am I trying to do?

I just noticed you posted the earlier version of the file not the one with the new formula that takes care of 3 columns is that what you want or should we update it with the new formula ?

Then I would appreciate you explain in plain English what you want to achieve then I will design a vba for you. Not interested to know what you did copy paste delete last cells ....

pls advise
gowflow

Sure Sir,

Step 1 By clicking Explore Button - see Date in cell C1 if date is greater or equal to D1 then red highlighted range I42:M42 i.e. 5 cell addresses - add +1 in each cell address in sheet LT.Z.

Step 2 Delete 1 data range A1:C1 & copy past below data to A1 i.e. shift whole data to one row up by deleting 1 row data.

Step 3 New data in highlighted Cell I42:M42 same way +1 to sheet LT.Z....next i next I till 4 row data is available.

Step 4 copy date of Cell C1 & past to Sheet LT.Z Cell A1

End
Z-Score-V3.0.xlsm

If step 1 Cell D1 date is Greater then Cell C1 date then delete data till that date & then proceed. if whole data is older than Cell D1 then Delete whole data & End.


Just for information - my calculation required 4 data set point so process last stage is last 4 row data in Range A1:C4 & then End.


Thanks

I see you attached the new file that has the new formula however I do not understand the formula in row 40 !!!! you are combining Column in Letters with column number that could be misleading to a cell number !!!
AG33, BC55,BZ78
are all cells that are empty.

You maybe meant to put
AG215, BC215, BZ215

Right ???
or else pls clarify before I start looking at your explanation above.
gowflow

How dumb I am, yes sir you are right.
put
AG215, BC215, BZ215

Right

ok good. give me sometime to digest all this and will revert.
gowflow

Sure Sir and apology for my language as English is not my first language.

you say:

Step 1 By clicking Explore Button - see Date in cell C1 if date is greater or equal to D1 then red highlighted range I42:M42 i.e. 5 cell addresses - add +1 in each cell address in sheet LT.Z.

Are you sure it is cell I42 to M42 ??? or it is J42 to M42 ?? as you had put in Col I A and although it shows A215 do you want to always start from Col A this is why you put A in Col I ??

pls clarify
gowflow

it is B just put B in Cell I41.in sheet LT.Z it is Column called Scoring.

This things happed due to I had done whole thing in hastily, as I don't want to put you on hold & even my kid wants to play with papa........he don't leave me for 1 minute......just pressing keyboard keys, touching monitor...teasing with mouse...so on ..I know you will understand.....   :)

Well I think you are mistaken !!!

Let me see If I got this correctly

When we see that the date in cell C1 >= D1 in sheet Calculation then we take the address of the red cells in this example
B215, blank, AG215, BC215, BZ215 in sheet LT.z and we add to each of these cells 1 here in this example we will endup with
B215 = 1
AG215 = 1
BC215 = 1
BZ215 = 1

from what I saw in the current data you have in row 81 in Col B = 2 and then you have 1 in Col D and 1 in Col F so I presume that Col B is the Total of all columns RIGHT ??

If I am correct then Col B should hold a formula and not any value there right ?? If I am not correct pls clarify.

gowflow

Column B  = Value not formula, yes it is total of all columns, if you find 2 in column B then process add +1 in that so it will come to 3. it is up to you if you want to give formula =SUM(C81:CO81) or  adding by code. All your convenience.
 I would prefer value then formula bcoz it may increase size of workbook. I have 53 same kind of sheets.

Thank You

ok no problem for value but your explanation was wrong then !!!
it should be:
add 1 to each of J to M and for each one of these add 1 in Col B for the same row so in this example you would endup with
B215 =3
AG215 = 1
BC215 = 1
BZ215 = 1
and not
B215 = 1
AG215 = 1
BC215 = 1
BZ215 = 1

as you explained correct ???


Second issue what do you mean by:

Step 3 New data in highlighted Cell I42:M42 same way +1 to sheet LT.Z....next i next I till 4 row data is available.

What is this: till 4 row data is available. ???? In your sheet calculation we have data from
Row1 to Row 108

Do you want to only process Row 1 to Row 4 ?
Do you want to process row 1 to Row 108 - 4 ??

Pls clarify

gowflow

Regarding ,
B215 =3
AG215 = 1
BC215 = 1
BZ215 = 1
and not
B215 = 1
AG215 = 1
BC215 = 1
BZ215 = 1 bold italic is right bcoz for me it is only one swing & which scores 1 & rest are reaction of different percentage.


Step 3 process whole data from row 1 to row 108 but as one round over delete the one row oldest data & move up one row whole data so red highlighted result will change do the same as above.do this till we have four point data as my calculation (red highlighted) require 4 row data.

Thank You

I don't get it

you say this is correct
B215 = 1
AG215 = 1
BC215 = 1
BZ215 = 1 bold italic is right

then you say:
if you find 2 in column B then process add +1 in that so it will come to 3. it is up to you if you want to give formula =SUM(C81:CO81)

Aren't you contradicting yourself ??
gowflow

Sir gowflow,

instead of formula just put values & each & every hit in red highlighted cell = value 1 for all cell addresses.

any of cell address previously have value then add +1 in that value. i.e.
assume if in Sheet LT.Z B215 = 10 already then +1 = 11
assume                           AG215 = 5 then  6
Assume                           BC215 = 15 then 16
assume                           BZ215 = Blank i.e. no value then 1

Thanks

Sir gowflow,

Ignore this comment  =SUM(C81:CO81)

Thank You

ok for this of adding  1 it is clear.

as for this

Step 3 process whole data from row 1 to row 108 but as one round over delete the one row oldest data & move up one row whole data so red highlighted result will change do the same as above.do this till we have four point data as my calculation (red highlighted) require 4 row data.

It is not clear when you say:
... do the same as above.do this till we have four point data as my calculation (red highlighted) require 4 row data.

It is not correct as the data in red cells does not require 4 row of data in col A to C they are a result of values in cell I32 that is related to data in sheet FT and cells C1, C2 C3 I do not see C4 then you need only last 3 rows not 4 is this correct ???

gowflow

Sir gowflow,

Result require 4 row data, as Sheet Calculation - R32:R35 data comes from I3:P24.all are interlinked i.e. see cell J3 Formula it requires value of cell B4, same way Cell L3 Formula requires value of Cell B4 same way Cell N3 &P3.

So after one set done next would be ...see attached screen shot.


Thank You
Capture.PNG
Untitled.png

ok fine I hadn't seen the formula in Col J all is clear right now.

1) So let me recap before I start the coding, you will be coping new data in the col A to C so you make new runs I suppose ?

2) You mention you have 50+ sheets like this one reason why you need values not formulas are they like sheet Calculation ?

If you give me the whole picture then I can design the macro accordingly to avoid you copy paste some code that may or may not work in other sheets.

gowflow

Sir gowflow,

1) Yes

2) Yes same calculation, so I thought if we put actual sheet name in Cell E1 in sheet calculation i.e. LT.Z, so code identified where to put +1 values.


Thank You

2) then I have this:

a) will all the 50+ sheets will update the same sheet LT.Z or the name varies ?
b) Will there be 50+ sheet calculation with different names or it is 1 sheet Calculation where when the data finishes you copy paste new data then run again the macro etc ... ???

I need the names of sheets it is important at this stage. The macro is almost done I need to test

gowflow

No need to give me the name of the 50+ sheets but just to understand the principle.
gowflow

Sir gowflow,

a) there is 50+ Sheet Name like AA, AB ,  AC,
& its Scoring table Sheet AA.Z, AB.Z, AC.Z....So names varies
so total sheet would be 100+.

b)it is 1 sheet calculation where when data finishes I copy new data for other sheet like for sheet AC.Z (I ll change the formula in cell I32 accordantly i.e. indirect Function).& run the macro.

Not relate to your question.
This one time process for every sheet after that all have to done from Member sheet i.e. AC, AB.....without .Z in end. That will be my next question.

Thank You

ok fine here it is:

1) I made a copy of Calculation and LT.Z sheet and called it (bckup) so you can compare data you may delete these 2 when all is ok.

2) WE cannot delete first row as it messes up all formulas but I got your logic there by a twist

3) Activate macros and Run the button Explore in the Sheet Calculation and see the results in sheet LT.Z

if all is ok then E1 as you requested has the sheet name

BE CAREFUL when you add a new SHEET you can mess up formulas if you do it the wrong way

Let me know
gowflow
Z-Score-V3.0.xlsm

Sir gowflow,

it seems working perfectly but I had press button again after all calculation done there is some kind of debugging message popped up & row data is deleted (only 2 row data is available)
, there is some issue that affect me in future? else I don't mind, code is perfect.

I dint see any code line for date copy past in sheet LT.Z Cell A1 as it is required, as per our example it will be 03 Jun 2013.(which is our oldest data point date after calculation done).

yes I tested by changing sheet name to AC.Z & in calculation sheet cell E1 = AC.Z it is working perfect.

Thank You

Sir gowflow,

What happen if in sheet calculation cell D1, date is 1 jun 2013 & data is available in column A B C is start from 1 jan 2013. it will delete the data till current date is leaser than data date & then proceed calculation? D1<C1,<= is not required as it may conflict & recalculate the same data. so I had

change this line

'---> Check to see if C1>D1
    If DateValue(WSCal.Cells(1, "C")) > DateValue(WSCal.Cells(1, "D")) Then

Select allOpen in new window

Thanks

What happen if in sheet calculation cell D1, date is 1 jun 2013 & data is available in column A B C is start from 1 jan 2013. it will delete the data till current date is leaser than data date & then proceed calculation? D1<C1,<= is not required as it may conflict & recalculate the same data. so I had

You had requested that

Step 1 By clicking Explore Button - see Date in cell C1 if date is greater or equal to D1 then

so now if you need it to be > and not >= just change it like you did.

As far as running the macro twice you are correct. Please replace the Macro Explore by this version.

Sub Explore()

Dim WSCal As Worksheet
Dim WSLTZ As Worksheet
Dim MaxRowCal As Long, I As Long

'---> Set Variables
Set WSCal = ActiveSheet
MaxRowCal = WSCal.Range("A1").End(xlDown).Row

On Error Resume Next
Set WSLTZ = Sheets(Format(WSCal.Range("E1")))
If Err <> 0 Then
    MsgBox ("Error occured and program will be halted." & Error(Err))
    Exit Sub
End If
On Error GoTo 0

I = 0
'---> Start Process
Do While MaxRowCal > 4
    '---> Check to see if C1>=D1
    If DateValue(WSCal.Cells(1, "C")) > DateValue(WSCal.Cells(1, "D")) Then
        
        '---> Assign Value of 1 to Existing Value in all of Red Cells
        '     in their coresponding cells in sheet LT.Z
        If WSCal.Range("I42") <> "" Then WSLTZ.Range(WSCal.Range("I42").Value) = WSLTZ.Range(WSCal.Range("I42").Value) + 1
        If WSCal.Range("J42") <> "" Then WSLTZ.Range(WSCal.Range("J42").Value) = WSLTZ.Range(WSCal.Range("J42").Value) + 1
        If WSCal.Range("K42") <> "" Then WSLTZ.Range(WSCal.Range("K42").Value) = WSLTZ.Range(WSCal.Range("K42").Value) + 1
        If WSCal.Range("L42") <> "" Then WSLTZ.Range(WSCal.Range("L42").Value) = WSLTZ.Range(WSCal.Range("L42").Value) + 1
        If WSCal.Range("M42") <> "" Then WSLTZ.Range(WSCal.Range("M42").Value) = WSLTZ.Range(WSCal.Range("M42").Value) + 1
    End If
    
    '---> Move all data 1 row up for Col A to C
    WSCal.Range("A2:C" & MaxRowCal).Copy WSCal.Range("A1")
    WSCal.Range("A" & MaxRowCal & ":C" & MaxRowCal).ClearContents
    MaxRowCal = WSCal.Range("A1").End(xlDown).Row
    
    '---> Incr Counter
    I = I + 1
    
Loop

MsgBox ("Exploring completed successfully for " & I & " Rows")

End Sub

Select allOpen in new window

gowflow

Sir gowflow,

Step 4 is still missing.

Thanks

ooops sorry overlooked this one.

here is the new code with step4.

Sub Explore()

Dim WSCal As Worksheet
Dim WSLTZ As Worksheet
Dim MaxRowCal As Long, I As Long

'---> Set Variables
Set WSCal = ActiveSheet
MaxRowCal = WSCal.Range("A1").End(xlDown).Row

On Error Resume Next
Set WSLTZ = Sheets(Format(WSCal.Range("E1")))
If Err <> 0 Then
    MsgBox ("Error occured and program will be halted." & Error(Err))
    Exit Sub
End If
On Error GoTo 0

I = 0
'---> Start Process
Do While MaxRowCal > 4
    '---> Check to see if C1>=D1
    If DateValue(WSCal.Cells(1, "C")) >= DateValue(WSCal.Cells(1, "D")) Then
        
        '---> Assign Value of 1 to Existing Value in all of Red Cells
        '     in their coresponding cells in sheet LT.Z
        If WSCal.Range("I42") <> "" Then WSLTZ.Range(WSCal.Range("I42").Value) = WSLTZ.Range(WSCal.Range("I42").Value) + 1
        If WSCal.Range("J42") <> "" Then WSLTZ.Range(WSCal.Range("J42").Value) = WSLTZ.Range(WSCal.Range("J42").Value) + 1
        If WSCal.Range("K42") <> "" Then WSLTZ.Range(WSCal.Range("K42").Value) = WSLTZ.Range(WSCal.Range("K42").Value) + 1
        If WSCal.Range("L42") <> "" Then WSLTZ.Range(WSCal.Range("L42").Value) = WSLTZ.Range(WSCal.Range("L42").Value) + 1
        If WSCal.Range("M42") <> "" Then WSLTZ.Range(WSCal.Range("M42").Value) = WSLTZ.Range(WSCal.Range("M42").Value) + 1
    End If
    
    '---> Move all data 1 row up for Col A to C
    WSCal.Range("A2:C" & MaxRowCal).Copy WSCal.Range("A1")
    WSCal.Range("A" & MaxRowCal & ":C" & MaxRowCal).ClearContents
    MaxRowCal = WSCal.Range("A1").End(xlDown).Row
    
    '---> Incr Counter
    I = I + 1
    
Loop

'---> Copy Cell C1 in Calculation to Cell A1 in LT.Z
WSLTZ.Range("A1") = WSCal.Range("C1")

MsgBox ("Exploring completed successfully for " & I & " Rows")

End Sub

Select allOpen in new window

gowflow

sir gowflow,


Almost done just need to clarify & reconfirm - see attached file - Run explore button.

A) what if Cell C1 Date is = Cell D1. data will process for that 4 point.in code i had changed >= to >. and actully it is not process, which is i want.

B)what if Cell C1 date is > cell D1. as in attached sheet i had manually put 22 may 2013. as per condition after that data should be processed ..(which is not processing ) & after that copy date of C1 & past to Sheet LT.Z A1 - End

Thanks
Z-Score-V3.0.xlsm

B)what if Cell C1 date is > cell D1. as in attached sheet i had manually put 22 may 2013. as per condition after that data should be processed ..(which is not processing ) & after that copy date of C1 & past to Sheet LT.Z A1 - End

Sorry I have trouble understanding what you want. Please restate what you want it is not clear what is the problem.

gowflow

Sir gowflow,

what if i change this line

Do While MaxRowCal > 4

Select allOpen in new window

To

Do While MaxRowCal >= 4

Select allOpen in new window

it leads to desire result...?

Thank You

I do not see any problem if you do this then you will endup with 3 rows not 4 is this ok with you ???

I just tried it and it ended up with 3 rows if this is fine with you then go ahead and do it.
gowflow

Sir gowflow,

As if you run my attached workbook where i put 22 may 13 then after that date data should be  processed. irrespective of last 4 data point. & then copy date C1 - past to A1 sheet LT.Z.
in current condition i don't find any data sheet LT.Z if i put 22 may 13 in LT.Z - Cell A1& run the exploration.there must be 1set of data in LT.Z

Thanks

If you want to keep 4 rows then do not change the code but put the date to be 21-May-2013 and not 22-May-2013 this way it will process 1 record which is 22-May-2013 and still you will end-up with 4 rows of data.

gowflow

Sir gowflow,

But in this case

I do not see any problem if you do this then you will endup with 3 rows not 4 is this ok with you ???

I just tried it and it ended up with 3 rows if this is fine with you then go ahead and do it.

in LT.Z sheet must have only one data set process value but if i do so there is 2 data set of values.

Thanks

I DO NOT UNDERSTAND WHAT YOU WANT

please speak English
gowflow

di you read my comment in ID: 39273963


gowflow

Extremely sorry sir i had run old version of code sorry.code run perfectly.

grrrrrrrrrrrrrrr

Sir gowflow,

i am in touch with you since yesterday 4:00 Pm to 1:30 PM almost 24 hours, i just wondered when you are going to sleep. i can see your dedication. One can pay back the loan of gold, but one dies forever in debt to those who are kind. & in my sense Sir gowflow is kindest. You doesn’t loose your  temper & come up with great solution. i had done whole thing manually & it has taken  3 hours to complete but after this code it is done in fraction of second. Sir gowflow you owe me.


Thank You Very Much

grrrrrrrrrrrrrrr

Sorry Sorry apology.

TKs your kind comments. My pleasure to help.
PLs feel free to post here any link to any other question you feel you may need my help with.
Rgds/gowflow

Actually i have 3 to 4 question in line for same workbook but i feel shy to ask help for again & again.but thanks for

TKs your kind comments. My pleasure to help.
PLs feel free to post here any link to any other question you feel you may need my help with.

Definitely i will,just need to mock up on question & get back you.


Thanks a lotttttttt  :)

No problem I will keep this question open pls paste the link in here as soon as you post it and ask it as a related question to this one like post in your new question a link to this question and say continuation to previous question and just summarize what you need.

Will wait for your link.
gowflow

One more thing please keep your question simple concise and to the point even if this means breaking it up into smaller questions so we can advance faster and more securely.

I read one of your past question that you are deleting now and it is correct like the Expert mentioned it is rather a project than a question. You will not find help here if you get your questions too complicated.

I do not have a problem but my comment is addressed to most experts here in general if they don't grasp what you want in the first 15 seconds then they don't even bother looking at your question.

gowflow

Got it Sir .....One more thing ....if you wish to.... can we communicate in any messenger so it will be super fast & to the point. if you wish to ..i am even fine with current procedure.

My Google ID is "itjockey" email itjockey@mail.com.


Thank You

Sorry rules on EE are very strict !!!!

if you communicate outside EE for issues realted to EE you will loose your membership. I personally value my membership in EE and do not wish to jeoperdise it.

HOWEVER, you are free to choose between communicating outside of EE or via EE but you cannot mix both.

I personally offer paid services if you choose to go the outside EE route. Just let me know your preference and will work accordingly.

gowflow

For this I have to communicate with EE customer care, even i also want to be with EE.After they don't have any issue and if I have that kind of question defiantly I go for second option.

Thanks

What is second option ?

As long as you don't mix issues between outside EE and EE then there is no problem.

The idea is that when you ask questions here all Experts have to have equal chances to answer and you cannot use outside communications to favor 1 Expert that would have an advantage on the others when it comes to answering questions.

You are free to break your questions into smaller and related questions and this is totally legal and if someone is following your case and is aware of it can answer so this is no problem as long as you are not communicating information outside EE that would favor this Expert over the other Experts.

Hope above clarifies.
gowflow

Sir gowflow, I am in stat of framing new question. Before I post I just need to know do u understand any part of this link Image

Thank You

Well these are designs with dimentions right ? but don't ask me more I don't know
gowflow

ok Sir got it...

Thanks

Just to know do you have the intention to post the question now ? or later ?
gowflow

You say do you want me to post now or later? if you want me to post now it will take 20 min to post ....

Thanks

ok fine no problem just to know. Post whenever you want. If you post now I am available and can assist you.
gowflow

it is taking much time  than I expected if I ready with, I ll Remind you here, as soon as you reply I will post new question & provide you the link.


Thanks

ok no problem.
gowflow

Any news ?
gowflow

Sir gowflow,

In process as I have construct question in such a manner so that even if person doesn't know the topic still he can able to help me on it. Yesterday I cant frame the question as whole day I feel sleepy. I am working on it now.


Thank You

I thought you wanted to give me an adavantage :::: :)
just kidding

If it is not for now your question let me know.
gowflow

Dint get you....

Sir goflow,

For sure before posting I will remind you, if you reply me back then I will post & as soon as I will post  new one, link will be here. Even I want you to assist me in my next question as it is easy for you to understand.


Thanks

ok no problem then
gowflow

Sir gowflow,


R u there?

Thanks

pass ee

pass bb

yes here. We have time lag I suppose.
gowflow

Sir gowflow,


Are You There? Your location?



Thanks

Sorry for not answering was sleeping !! now it is morning here.
gowflow

Available?

Available?

yes was Sunday ystday.
gowflow

ok before i start i what to clarify that this will take plenty amount of your time........Still you want to continue...?

well 1 question can hold so much !!! if 1 question hold a story, then this is then a PROJECT then you ought to break this into smaller pieces (questions that could be part1, part2 etc..). As I noticed you not only ask questions here but also answer some so you know fair well that a question still need to be a question not a project.
gowflow

About to post link in 5 Min...thanks

Scoring simplified

gowflow

file is password protected.
gowflow

Sir gowflow,

This regarding this question. I just need to know if you run Code how many counts you find in scoring table, as per my counting manually it must  be449, But in =SUM(B505:B883) in sheet LT. I got result 448 but there is 552 data point so score must be 449.if I don't put any data in Column A i.e. 1 2 3 4 5 6 code doesn't work. Pass "vv"



Thanks
Z-Score-V3.2.xlsm

1)
What tells you that the sum is 449 ?? maybe you made a mistake somewhere !!! indeed the formula shows 448.

Now what you need to do as a test is to delete all data in Col B to max extreme right then run a piece of data and see if the total is correct.

2)
what do you mean by:
if I don't put any data in Column A i.e. 1 2 3 4 5 6 code doesn't work.

What Col A ? in what sheet ? Calculation or LT.Z ??
I designed the code based on the data you provided if you change a whole column after then for sure will need to recheck.

I suspect this instruction
MaxRowCal = WSCal.Range("A1").End(xlDown).Row

It take the last row of the file based on data in Col A if Col A does not hold data then you can use this instruction.

The new code below is based on Col C that will always hold data and you can test it and remove data in Col A and check the results.

Sub Explore()

Dim WSCal As Worksheet
Dim WSLTZ As Worksheet
Dim MaxRowCal As Long, I As Long

'---> Set Variables
Set WSCal = ActiveSheet
MaxRowCal = WSCal.Range("C1").End(xlDown).Row


On Error Resume Next
Set WSLTZ = Sheets(Format(WSCal.Range("E1")))
If Err <> 0 Then
    MsgBox ("Error occured and program will be halted." & Error(Err))
    Exit Sub
End If
On Error GoTo 0

I = 0
'---> Start Process
Do While MaxRowCal > 4
    '---> Check to see if C1>D1
    If DateValue(WSCal.Cells(1, "C")) > DateValue(WSCal.Cells(1, "D")) Then
        
        '---> Assign Value of 1 to Existing Value in all of Red Cells
        '     in their coresponding cells in sheet LT.Z
        If WSCal.Range("I42") <> "" Then WSLTZ.Range(WSCal.Range("I42").Value) = WSLTZ.Range(WSCal.Range("I42").Value) + 1
        If WSCal.Range("J42") <> "" Then WSLTZ.Range(WSCal.Range("J42").Value) = WSLTZ.Range(WSCal.Range("J42").Value) + 1
        If WSCal.Range("K42") <> "" Then WSLTZ.Range(WSCal.Range("K42").Value) = WSLTZ.Range(WSCal.Range("K42").Value) + 1
        If WSCal.Range("L42") <> "" Then WSLTZ.Range(WSCal.Range("L42").Value) = WSLTZ.Range(WSCal.Range("L42").Value) + 1
        If WSCal.Range("M42") <> "" Then WSLTZ.Range(WSCal.Range("M42").Value) = WSLTZ.Range(WSCal.Range("M42").Value) + 1
    
        '---> Incr Counter
        I = I + 1
    End If
    
    '---> Move all data 1 row up for Col A to C
    WSCal.Range("A2:C" & MaxRowCal).Copy WSCal.Range("A1")
    WSCal.Range("A" & MaxRowCal & ":C" & MaxRowCal).ClearContents
    MaxRowCal = WSCal.Range("C1").End(xlDown).Row
    
    
Loop

'---> Copy Cell C1 in Calculation to Cell A1 in LT.Z
WSLTZ.Range("A1") = WSCal.Range("C1")

MsgBox ("Exploring completed successfully for " & I & " Rows")

End Sub

Select allOpen in new window

gowflow

I am in my way to office ....i surly reply you soon....thanks

Sir gowflow,

See attached capture screens.

I had tried your new code but it too slow in executing, previous one is lighting fast compare to this one so I go for previous code.



Thanks
Capture.PNG
2.PNG

I did not change anything in the code. I think you are mistaken in the 448 and 449.

What I conclude from the picture you posted the remaining lines in Calculation are 449, 490, 491, 492 Correct ??? then what has been loaded in the sheet LT or whatever is from line 1 to 448 this means the total being 1 for each line it is 448 if: ALL OF THE ITEMS HAVE A DATE > THAN THE DATE IN C1 !!!

AM I CORRECT ???

Now for the code being too slow I did not change anything but replaced
this line
MaxRowCal = WSCal.Range("A1").End(xlDown).Row

by this line
MaxRowCal = WSCal.Range("C1").End(xlDown).Row


on both line 9 and line 41 and it should not affect performance at all. Don't know what is happenening on your workbook that is maybe kicking in when you run this macro that is slowing down.

gowflow

Got it Sir Should we go to next step, did you seen attached file ? did you understand what am i trying to do? not whole but just general idea..?

Is this one closed here ? like is the last code working fine or you still have problems on this question ???

lets keep each question separate.
gowflow

Close this one....


Thnaks

gowflow

ok fine
gowflow

"gowflow"

pass word "gowflow"

Hi Sir,