Link to home
Start Free TrialLog in
Avatar of Naresh Patel
Naresh PatelFlag for India

asked on

VBA Help

Hi experts,

i need a help with VBA code which import data (copy data from other workbook sheets one by one) apply some formula and create new WB for that process data ...then go for next sheet - formula - Create .....next sheet till last sheet of target Wb.
 
process like this -
Working on WB Data Process.xlsm....on button click ---
A) clear all data from sheet Import & from sheet Create range A2:I till end.
1) From path D:\WIP Historicals\Raw.xlsx WB --- from Sheet first ---- copy data - range Column A:I & past to Sheet Import. (sheet first is not name of sheet ...i really mean Wb 1st sheet :)    )
2) Paste this formula in Cell J2
=IF(COUNTIF(C2:F2,"#N/A N/A")=4,"Ignore","True")

Open in new window

& Copy down till end.
3) Paste this formula in cell K6
=IF(AND(COUNTIF(J2:J6,"Ignore")=5,J6="Ignore"),"Waite","Start")

Open in new window

& copy down till end.
4) Paste this formula in cell L6
=IF(K6="Start",$A$2,"")

Open in new window

& copy down till end.
5) Paste this formula in cell M6
=IF(L6<>"",B6,"")

Open in new window

& copy down till end.
6) Paste this formula in cell N6
=IF(L6<>"",IF(AND(C6="#N/A N/A",ISNUMBER(D6)),D6,IF(AND(C6="#N/A N/A",D6="#N/A N/A"),F6,IF(AND(C6="#N/A N/A",D6="#N/A N/A",F6="#N/A N/A"),E6,C6))),"")

Open in new window

& copy down till end.
7) Paste this formula in cell O6
=IF(L6<>"",IF(AND(D6="#N/A N/A",ISNUMBER(C6)),C6,IF(AND(C6="#N/A N/A",D6="#N/A N/A"),F6,IF(AND(C6="#N/A N/A",D6="#N/A N/A",F6="#N/A N/A"),E6,D6))),"")

Open in new window

& copy down till end.
8) Paste this formula in cell P6
=IF(L6<>"",IF(AND(E6="#N/A N/A",ISNUMBER(F6)),F6,IF(AND(E6="#N/A N/A",F6="#N/A N/A"),C6,IF(AND(E6="#N/A N/A",F6="#N/A N/A",C6="#N/A N/A"),D6,E6))),"")

Open in new window

& copy down till end.
9) Paste this formula in cell Q6
=IF(L6<>"",IF(AND(F6="#N/A N/A",ISNUMBER(E6)),E6,IF(AND(E6="#N/A N/A",F6="#N/A N/A"),C6,IF(AND(E6="#N/A N/A",F6="#N/A N/A",C6="#N/A N/A"),D6,F6))),"")

Open in new window

& copy down till end.
10) Paste this formula in cell R6
=IF(G6="#N/A N/A",0,G6)

Open in new window

& copy down till end.
11) Paste this formula in cell S6
=IF(R6=0,0,ROUNDDOWN(H6,0))

Open in new window

& copy down till end.
12) Paste this formula in cell T6
=IF(ISNUMBER(I6),I6,"")

Open in new window

& copy down till end.
13) Now in sheet Import copy all --- past special - Values.
14) Remove all rows where column J="Ignore"
15) Find 1st non blank row in column L ---copy data range L:T from that row till end past to sheet Create Cell A2.
16) Copy sheet Create ---past to  new Wb ---- mention tab as Cell A2 & file name as cell A2 --- file extension is .csv - Path same as active Wb.
17) Step A
18) Go for next sheet till end

Thanks
Data-Process.xlsm
Raw-Data.xlsx
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Nice explained itJockey :)
Avatar of Naresh Patel

ASKER

Do you Mean.... i nicely Asked??? ;)
Itjockey,

Please explain What it should do in 17) Step A & 18) Go for next sheet till end
Are you serious in getting code? Else I would request to delete this question.
Avatar of Bill Prew
Bill Prew

It feels like you might be re-inventing the wheel.  There are so many options for stock and portfolio management out there, why not select and use one of those?  A number are free too.  And then there are desktop packages like Quicken that can track stock performance.  Online things like Morningstar, Yahoo Finance, Mint, Personal Capital, Google Finance - it seems like there could be something that can gather and summarize the data already, once you tell it the tickers you want to track.  Or am I missing something?

~bp
Bill,

I have already created a code for all of his process from 1 to 16, I just want to know about 17 & 18. Else its worthless effort.
I interpreted 17 and 18 to mean that he wants to do the same set of steps for each of the sheets in RAW-DATA workbook.  Although as I reread I guess it isn't clear since for each sheet he indicates wipe out all the previously imported data.  So not sure if they want separate summary sheets, or one merged from all sheets in RAW, or what...  Guess we need itjockey to speak up.

~bp
opppsss extremely sorry for delay ...let me read all post and revert to you guys ....apology
itjockey,

I even sent you few PM's asking same thing, but you never responded.
@Shums, For your This Commennt.

Basically i have data in RAW WB for each Ticker (Symbol) in separate Sheets there are 1850 sheets each sheet contain data for one ticker  ...i need to create separate WB for each sheet. the data there is many #N/A Values i need to remove that errors that is why long list of formula in my original 1st post.

Step 17 means after creating new WB for 1st sheet ....go for next sheet but there is already data of 1st sheet so need to clear that data & then import 2nd sheet data. in step A i already mention that .

Step 18 is cycle of Step A to step 17 till last sheet of WB Raw Data.

Still Any question ...let me know .

Thanks
@ Shums ....apology buddy
@ Bill Prew For you Comment.Comment

Agreed There is Free Data Available. but if i update all tickers in my charting software which m i looking to ...it will take 1 hour every day ....i have set up to download daily data and update my charting software directory in 1 minute. but things get worsen in period of time ...there is stock spits ...bonus ...face values change ....i cant get proper picture of that particular ticker ....so to overcome this i had downloaded fresh spit merge adjusted data in excel for each symbol ...but this data have some values like #N/A which i charting software cant read ...& even my charting software read one WB one ticker ....so that is why i need to separate WB for Each ticker with clean data ...

Thanks
@Bill

I Guess there is no wipe out of data as Step 16 Did creation of New WB For Each Ticker. i might missing something then please let me know.

Thanks
Hold on buddy, after 3 days you are in action, let me refresh my memory, what I did in that code. LOL
:) Sorry to say but this days my life being so hectic... my self as financial market devotee ....currently applying my logic in manufacturing production line. i know that is not your concern .....accepted i am late in reply. LOL
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks
You're Welcome! Glad I was able to help