Solved

Scroing

Posted on 2014-02-10
27
140 Views
Last Modified: 2014-02-13
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
0
Comment
Question by:itjockey
  • 14
  • 13
27 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
1 Yes Sir,

2 Skip that row start with row 4.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Yes Sir
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Closing Comment

by:itjockey
Comment Utility
Perfect!!!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Great !
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok no problem let me know
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ok Sir
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
pls do not forget to post the link here of the new question
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
May I Post Question Now?
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
As & when you give me thumbs Up I will post then only....

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok will let you know should be in around 1:30 hours or less.
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Ready
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
here it is Scoring

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Ok Sir got it.
0

Featured Post

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.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

14 Experts available now in Live!

Get 1:1 Help Now