Scroing

Sir gowflow,

This is last part of this question series.
I want to code who copy rows from location "C:\Data G\Test.xlxs".& past to A32

Step 1 Copy Row from "C:\Data G\Test.xlxs"  range A4:L5 . & past to A32
Step 2 Run analyze Sub
Step 3 Copy Row From "C:\Data G\Test.xlxs"  range A5:L6 . & past to A32
Step 4 Run analyze Sub
Step 5 Copy Row From "C:\Data G\Test.xlxs"  range A6:L7 . & past to A32

Till End. I guess you got my point.

Thanks
Scoring-G-V06.xlsm
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
You mean to say you want to copy from the file Test.xlsx to the present workbook in first sheet Row 32 ...

If yes could you post sample of Test.xlsx
gowflow
0
Naresh PatelTraderAuthor Commented:
Sir you can use this file "xyz.xlxs" from this question link as I am not in front of my machine or I will on my next login.
http://mobile.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28357588.html. Thanks
0
gowflowCommented:
1) Well the file you posted has Analyse sub but it is not the one last posted. Did you happen to change it ?

2) How do you want to deal with the first record that have values like Start and not figure as it give an error in the formula and the macro stops. Do you want to replace Start by 0 is it ok for your calculations ?

gowflow
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Naresh PatelTraderAuthor Commented:
1 Yes Sir,

2 Skip that row start with row 4.

Thanks
0
gowflowCommented:
1) Yes Sir,
>>> Well it is changed all in all. Do you trust the logic ? Are the results fine ?
I believe for troubleshooting purposes it is not healthy to have such a looooooonnnnggg sub like this it has to be split.

If you agree and I believe this would be in your favor as I just saw that you added new tables is for you object of a new question to simply explain the logic behind each round as I see you have several passes of high/low for different Columns. and then basis which I can simplify all this for you to have new amendments and troubleshooting much easier.

I am afraid that maybe a small error in this long routine as it stands right now will take couple of days to troubleshoot.

2) I am not in favor (principle and programing wyse to forgo a row) although you may better know, but now it could be the first that is start ... but maybe lateron it is not the start.
So I already amended the routine to replace anything that is not a figure by 0 (zero)

So my question to you is a zero damaging for you when it is not a figure or you prefer a 1 ??

gowflow
0
Naresh PatelTraderAuthor Commented:
1) Yes Sir,
>>> Well it is changed all in all. Do you trust the logic ? Are the results fine ?
I believe for troubleshooting purposes it is not healthy to have such a looooooonnnnggg sub like this it has to be split.

If you agree and I believe this would be in your favor as I just saw that you added new tables is for you object of a new question to simply explain the logic behind each round as I see you have several passes of high/low for different Columns. and then basis which I can simplify all this for you to have new amendments and troubleshooting much easier.

I am afraid that maybe a small error in this long routine as it stands right now will take couple of days to troubleshoot.

all result is fine - but I don't know Such looooong Sub healthy or not. So I am asking you do u want me to put new question which include all to gather? but then it will too long description. pls suggest.

2) I am not in favor (principle and programing wyse to forgo a row) although you may better know, but now it could be the first that is start ... but maybe lateron it is not the start.
So I already amended the routine to replace anything that is not a figure by 0 (zero)

So my question to you is a zero damaging for you when it is not a figure or you prefer a 1 ??

as you understand  whole process it Is called back testing - & I am counting hits as per historical data - so there is no room for assume data (0 or 1 ) so if there is "start" then we have ignore that row, as it will produce wrong result. Yes in future there is the case of adding data to row  (Test.xlsx) but it will at the end of the data - older to newer date. & there is always Start at row 3.

Thanks


Thanks
Test.xlsx
0
gowflowCommented:
ok here is how we proceed. I will now answer item 2) as it is object of this question then will comment on item 1).

I will modify the Import from file to check each row imported (prior to copying in 32 and 33) where all items should be numeric. If any item or field is not numeric it will not import the row.

Do you agree on this ?
gowflow
0
Naresh PatelTraderAuthor Commented:
Yes Sir
0
gowflowCommented:
ok here it is:

I created a new Function called
RowIsNumeric

 
Function RowIsNumeric(Rng As Range) As Boolean
Dim cCell As Range


For Each cCell In Rng
    If Not IsNumeric(cCell) And Not IsDate(cCell) Then
        RowIsNumeric = False
        Exit Function
    End If
Next cCell

RowIsNumeric = True
End Function

Open in new window


its purpose is to check if the row has any field (beside Col A and C) that are non numeric then it will flag this row for no processing.

Also here is an updated version of the previous ProcessWB that is now called ImportData

Sub ImportData()
Dim WB As Workbook
Dim WS As Worksheet
Dim ThisWS As Worksheet
Dim MaxRow As Long, I As Long, lCount As Long, RowFm As Long, RowTo As Long
Dim WBName As String

With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Replace this with the exact path and file name of your file
'WBName = "C:\Data G\xyz.xlsx"
WBName = "C:\Users\JGE002\Desktop\EE Projects\itjockey\13 Scoring part3\xyz.xlsx"
Set ThisWS = ThisWorkbook.ActiveSheet
Set WB = Workbooks.Open(WBName)
Set WS = WB.Sheets(1)

MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
ThisWS.Range("A32:L33").ClearContents

For I = 3 To MaxRow
    '---> Update Row 32
    If RowIsNumeric(WS.Range("B" & I & ":L" & I)) Then
        WS.Range("A" & I & ":L" & I).Copy
        ThisWS.Range("A32").PasteSpecial xlPasteValues
    End If
    
    '---> Update Row 33
    If RowIsNumeric(WS.Range("B" & I + 1 & ":L" & I + 1)) Then
        WS.Range("A" & I + 1 & ":L" & I + 1).Copy
        ThisWS.Range("A33").PasteSpecial xlPasteValues
        lCount = lCount + 1
    End If
    
    '---> Analyse Data
    Analyse
    
    DoEvents
Next I

WB.Close savechanges:=False
Set WB = Nothing
Set WS = Nothing

With Application
    '.EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With


MsgBox ("Analyse and Import of entire workbook completed successfully for " & lCount & " points.")


End Sub

Open in new window


This routine will open the file to import from and process it.

I have attached the workbook, pls test it and let me know.
gowflow
Scoring-G-V07.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
As far as point 1) is concerned I would suggest (if you want to have a clean concise code) that is up to you (but I would favor this for your longterm run of this file and for whoever to be able to jump in easily and make addition deletions or modification to make the Sub Analyse more efficient.

For this you will need to simply put it in your own words of how the routine should run (without going in details like keep it to say Update Col A, B C etc...)
like you should be able to describe in summary what this routine do and basis this we can then modify the code to make it more concise as you see there are a lot of repetition of the same routines but with different variables. they can be grouped in Functions or Sub and called from the main Analyse routine.

If you agree then your job would be to simply explain what are the steps that need to update and what columns they envolve. You can then maybe name the tables differently as I see 5 tables that have the same name but I am sure each one means something.

gowflow
0
Naresh PatelTraderAuthor Commented:
Perfect!!!
0
gowflowCommented:
Great !
0
Naresh PatelTraderAuthor Commented:
Sure I will.....tomorrow, but I guess major part is over for this series may be 2 more question other than  you suggested on finalization of SO9. my next series is for Fourier analysis. if you wish to pls be a part of that.so all in all there is 3 way back testing. one we are working on "SO9" & Hust "Fourier" & Fib "Scoring Simplified"


Thanks
0
gowflowCommented:
ok no problem let me know
gowflow
0
Naresh PatelTraderAuthor Commented:
Sir let me know regarding this Comment. do u want me to explain exactly what I am doing or just
For this you will need to simply put it in your own words of how the routine should run
as if you wish to understand what exactly going on, I had created 5 page word doc (70% to 80% Graphs so not too much reading).

pls reply

Thanks You
0
gowflowCommented:
wow !!! 5 pages ??? NOOOOOOOO

Just like this:

Kindly make this Sub (Analyse) more freindly (and post the Sub) by pressing on the Code button in the question window it will give you

 [ quote ]
.. put you code here
 [ backslah quote ]

Explain here briefly what are the passes

Pass1
Update b6,b9etc,,,
and update Col C,D,E
in Table1
for High and low

Pass2
Update c7,b9,etc..
and update Col F,G,H
in Table2
for high and Low

...
etc...

something like this.
gowflow
0
Naresh PatelTraderAuthor Commented:
ok Sir
0
gowflowCommented:
pls do not forget to post the link here of the new question
gowflow
0
Naresh PatelTraderAuthor Commented:
May I Post Question Now?
0
Naresh PatelTraderAuthor Commented:
As & when you give me thumbs Up I will post then only....

Thanks
0
gowflowCommented:
ok will let you know should be in around 1:30 hours or less.
gowlfow
0
gowflowCommented:
Ready
0
Naresh PatelTraderAuthor Commented:
here it is Scoring

Thanks
0
gowflowCommented:
I would appreciate (as respect to all other Experts including yourself as I see you also answer questions) is to avoid posting a question specific to one Expert but as a question in general. Pls make sure you do not mention my nick or any other unless you want to say something like so and so developed for me .... or tks to so and so I have this routine that do x y z.

AND NOT
Sir gowflow,

Kindly make this Sub (Analyse) more friendly.
etc....

gowflow
0
Naresh PatelTraderAuthor Commented:
I would appreciate (as respect to all other Experts including yourself as I see you also answer questions) is to avoid posting a question specific to one Expert but as a question in general. Pls make sure you do not mention my nick or any other unless you want to say something like so and so developed for me .... or tks to so and so I have this routine that do x y z.

Understood.

AND NOT
Sir gowflow,

I guess this gesture of  respect which you earned  for me.

Thanks
0
gowflowCommented:
I appreciate it and thank you very much you can put this in the thread but not when posting a question as then it is considered only addressed to the specific expert and then it indicate a lack of respect for others which I am sure is not your intention.

gowflow
0
Naresh PatelTraderAuthor Commented:
Ok Sir got it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.