Solved

Scroing

Posted on 2014-02-10
27
143 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
ID: 39847151
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
ID: 39847223
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
ID: 39847759
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 8

Author Comment

by:itjockey
ID: 39848171
1 Yes Sir,

2 Skip that row start with row 4.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39848379
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
ID: 39849299
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
ID: 39849789
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
ID: 39849807
Yes Sir
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39849901
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
ID: 39849923
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
ID: 39849951
Perfect!!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39849955
Great !
0
 
LVL 8

Author Comment

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

Expert Comment

by:gowflow
ID: 39850638
ok no problem let me know
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39852842
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
ID: 39852852
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
ID: 39852858
ok Sir
0
 
LVL 29

Expert Comment

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

Author Comment

by:itjockey
ID: 39855732
May I Post Question Now?
0
 
LVL 8

Author Comment

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

Thanks
0
 
LVL 29

Expert Comment

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

Expert Comment

by:gowflow
ID: 39856042
Ready
0
 
LVL 8

Author Comment

by:itjockey
ID: 39856058
here it is Scoring

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39856173
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
ID: 39856193
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
ID: 39856246
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
ID: 39856343
Ok Sir got it.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

792 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