By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,781 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,781 IT Pros & Developers. It's quick & easy.

Having a problem with the following code...Please help!

P: 55
Below you will find the code that I have written. I am asking it to search for an Excel file. Once it finds the correct dated Excel file it opens it. From there it is in search of information. I can't determine if the macro is working without fixing the following date - 1 issue. Any assistance would be greatly appreciated. I am using Excel 2003.

Expand|Select|Wrap|Line Numbers
  1. Dim FileDate, Cusip, Here, Today, There
  2.     FileDate = InputBox("Enter File Date in yyyymmdd format")
  3.  
  4.     Set Today = Nothing
  5.     Set Today = Date - 1
  6.  
  7.     Set Cusip = Nothing
  8.     Set Here = Nothing
  9.     Set There = Nothing
  10.  
  11.     Workbooks.Open ("S:\fixed_income_etfs\outgoing\Archive\Trade Date " & FileDate & "\Fund_Performance_Report_" & FileDate & ".csv")
  12.     Windows("ETFPerfComp (Test)+.xls").Activate
  13.     Range("E3").Select
  14.     Set Cusip = ActiveCell
  15.     Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
  16.         Cells.Find(What:=Cusip, After:=ActiveCell, LookIn:=xlFormulas, _
  17.         LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
  18.         MatchCase:=False, SearchFormat:=False).Activate
  19.     ActiveCell.Offset(0, 1).Select
  20.     Set Here = ActiveCell
  21.     Selection.Copy
  22.     Windows("ETFPerfComp (test)+.xls").Activate
  23.  
  24.     Cells.Find(What:=CurrentDate, After:=ActiveCell, LookIn:=xlFormulas, _
  25.         LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
  26.         MatchCase:=False, SearchFormat:=False).Activate
  27.     Set There = Active.Cell
  28.     ActiveCell.Offset(-1, 10).Select
  29.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  30.         :=False, Transpose:=False
  31.  
  32.     Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
  33.     ActiveCell.Offset(0, 1).Select
  34.     Set Here = ActiveCell
  35.     Selection.Copy
  36.  
  37.     Windows("ETFPerfComp (test)+.xls").Activate
  38.     Range(There).Select
  39.     ActiveCell.Offset(-1, 4).Select
  40.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  41.         :=False, Transpose:=False
  42.  
  43.    Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
  44.     ActiveCell.Offset(0, 1).Select
  45.     Set Here = ActiveCell
  46.     Selection.Copy
  47.     Windows("ETFPerfComp (test)+.xls").Activate
  48.     Range(There).Select
  49.     ActiveCell.Offset(0, 11).Select
  50.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  51.         :=False, Transpose:=False
  52.    Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
  53.     ActiveCell.Offset(0, 1).Select
  54.     Set Here = ActiveCell
  55.     Selection.Copy
  56.     Windows("ETFPerfComp (test)+.xls").Activate
  57.     Range(There).Select
  58.     ActiveCell.Offset(0, 5).Select
  59.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  60.         :=False, Transpose:=False
Dec 18 '07 #1
Share this Question
Share on Google+
1 Reply


Dököll
Expert 100+
P: 2,364
Below you will find the code that I have written. I am asking it to search for an excel file once it finds the correct dated excel file it opens it. From there it is in search of information. I can't determine if the macro is working without fixing the following date - 1 issue. Any assistance would be greatly appreciated. I am using Excel 2003.
Greetings, eskelies!

Do you get an error when you run the code?

Please tell us what it is and we go from there. I cannot run anything now.

In a bit, and do stay tuned even if you have not heard from me, other experts may see your note and will respond.

Dököll
Dec 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.