Solved

Scoring Simplified

Posted on 2013-06-24
97
467 Views
Last Modified: 2013-12-28
Hi Expert,

I had recorded the macro with select A2:C2 - Ctrl - Down Arrow - Copy -  Past to A2 - Control Down - select last 3 cells - delete . but it is not performing as I wanted.

Cell I42:M42 yellow highlighted cells  which is cell address of sheet LT.Z. I want to put each & every cell address add 1.if there is already 3 in any cell then result must be 4 for each & every time I had clicked button "Explore".

See attached file.

Thank You
Z-Score.xlsm
0
Comment
Question by:itjockey
  • 53
  • 44
97 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39271799
will look at it now
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39271818
thanks but sir..... did you under stand what am I trying to do?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39271826
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39271996
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272013
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272151
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272173
How dumb I am, yes sir you are right.
put
AG215, BC215, BZ215

Right
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272196
ok good. give me sometime to digest all this and will revert.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272209
Sure Sir and apology for my language as English is not my first language.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272236
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272273
it is B just put B in Cell I41.in sheet LT.Z it is Column called Scoring.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272300
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.....   :)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272302
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272340
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272381
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272451
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272473
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272502
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272514
Sir gowflow,

Ignore this comment  =SUM(C81:CO81)

Thank You
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272709
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272778
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272868
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39272918
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39272997
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273018
No need to give me the name of the 50+ sheets but just to understand the principle.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273060
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273124
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273196
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273263
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

Open in new window


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273696

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

Open in new window



gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273719
Sir gowflow,

Step 4 is still missing.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273827
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

Open in new window



gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273882
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273902

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
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39273925
I re-read your comment 5 times and I think know what you want.

you put in cell A1 in LT.Z 22-May-2013
Note that all your dates in Col A of Calculation are 2008,2009,2010,2011 and 2012 are all dates that are smaller than 22-May-2013 so they will not be processed.

As from rows 97 you have dates in 2013 until row 104 you have still dates that are smaller than 22-May-2013 and will not be processed.

Row 104 is 22-May-2013 and also will not be proceesed as the formula is STRICTLY Bigger > then the program stops as it is the last 4 rows.

So no problem with the code.

I have put an amended version to give you a correct feedback on the rows that were processed.

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
    
        '---> 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("A1").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

Open in new window


gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273944
Sir gowflow,

what if i change this line
Do While MaxRowCal > 4

Open in new window


To

Do While MaxRowCal >= 4

Open in new window


it leads to desire result...?

Thank You
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273958
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273959
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273963
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273965
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273967
I DO NOT UNDERSTAND WHAT YOU WANT

please speak English
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273970
di you read my comment in ID: 39273963


gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39273971
Extremely sorry sir i had run old version of code sorry.code run perfectly.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39273974
grrrrrrrrrrrrrrr
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39273998
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274000
grrrrrrrrrrrrrrr


Sorry Sorry apology.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274001
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274012
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  :)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 29

Expert Comment

by:gowflow
ID: 39274020
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274037
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274061
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274068
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274223
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274235
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274238
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274251
Well these are designs with dimentions right ? but don't ask me more I don't know
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274258
ok Sir got it...

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274277
Just to know do you have the intention to post the question now ? or later ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274286
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274289
ok fine no problem just to know. Post whenever you want. If you post now I am available and can assist you.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39274333
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39274336
ok no problem.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39277147
Any news ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39277175
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39277453
I thought you wanted to give me an adavantage :::: :)
just kidding
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39282136
If it is not for now your question let me know.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39282277
Dint get you....
0
 
LVL 8

Author Comment

by:itjockey
ID: 39282300
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39283575
ok no problem then
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39284782
Sir gowflow,


R u there?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39284784
pass ee
0
 
LVL 8

Author Comment

by:itjockey
ID: 39285188
pass bb
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39286256
yes here. We have time lag I suppose.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39287212
Sir gowflow,


Are You There? Your location?



Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39287831
Sorry for not answering was sleeping !! now it is morning here.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39287957
Available?
0
 
LVL 8

Author Comment

by:itjockey
ID: 39289103
Available?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39289204
yes was Sunday ystday.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39289233
ok before i start i what to clarify that this will take plenty amount of your time........Still you want to continue...?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39289297
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39289499
About to post link in 5 Min...thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39289506
0
 
LVL 8

Author Comment

by:itjockey
ID: 39289530
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39289753
file is password protected.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39291635
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39292467
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

Open in new window



gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39292617
I am in my way to office ....i surly reply you soon....thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39293659
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39293923
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39294689
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..?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39296326
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39296341
Close this one....


Thnaks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39296441
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39296758
ok fine
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39296782
"gowflow"
0
 
LVL 8

Author Comment

by:itjockey
ID: 39297296
pass word "gowflow"
0
 
LVL 8

Author Comment

by:itjockey
ID: 39743396
Hi Sir,Happy New Year
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now