| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Wrongftype macro
From excel 2003 menu Tools - Macro - Start Recording or 2007 View Macros - Start Recording The macro can be found by type Alt F11 to get to VBA and clicking on Module1 in the VBA project window "Greenwind" wrote: I went to Tool -- Microsoft Script Editor and I got below form the Macro (called 'Macro1') and I saved it in the Personal Macro Workook, which is a folder called 'Personal'. Thank you so much! <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40" <head <meta http-equiv=Content-Type content="text/html; charset=gb2312" <meta name=ProgId content=Excel.Sheet <meta name=Generator content="Microsoft Excel 11" <link rel=File-List href="PERSONAL_files/filelist.xml" <link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso" <link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso" <!--[if gte mso 9]<xml <o:DocumentProperties <o:Author0710058c</o:Author <o:LastAuthor0710058c</o:LastAuthor <o:Created2009-06-06T14:50:25Z</o:Created <o:LastSaved2009-06-06T14:53:26Z</o:LastSaved <o:CompanyUoG</o:Company <o:Version11.9999</o:Version </o:DocumentProperties </xml<![endif]-- <style <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} tr {mso-height-source:auto;} col {mso-width-source:auto;} br {mso-data-placement:same-cell;} .style0 {mso-number-format:General; text-align:general; vertical-align:bottom; white-space:nowrap; mso-rotate:0; mso-background-source:auto; mso-pattern:auto; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:134; border:none; mso-protection:locked visible; mso-style-name:Normal; mso-style-id:0;} td {mso-style-parent:style0; padding-top:1px; padding-right:1px; padding-left:1px; mso-ignopadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:134; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} -- </style <!--[if gte mso 9]<xml <x:ExcelWorkbook <x:ExcelWorksheets <x:ExcelWorksheet <x:NameSheet1</x:Name <x:WorksheetOptions <x:CodeName</x:CodeName <x:Selected/ <x:ProtectContentsFalse</x:ProtectContents <x:ProtectObjectsFalse</x:ProtectObjects <x:ProtectScenariosFalse</x:ProtectScenarios </x:WorksheetOptions </x:ExcelWorksheet </x:ExcelWorksheets <x:WindowHidden/ <x:WindowHeight8445</x:WindowHeight <x:WindowWidth13275</x:WindowWidth <x:WindowTopX480</x:WindowTopX <x:WindowTopY60</x:WindowTopY <x:ProtectStructureFalse</x:ProtectStructure <x:ProtectWindowsFalse</x:ProtectWindows </x:ExcelWorkbook </xml<![endif]-- </head <body link=blue vlink=purple <table x:str border=0 cellpadding=0 cellspacing=0 width=64 style='border-collapse: collapse;table-layout:fixed;width:48pt' <col width=64 style='width:48pt' <tr height=17 style='height:12.75pt' <td height=17 width=64 style='height:12.75pt;width:48pt'</td </tr <![if supportMisalignedColumns] <tr height=0 style='display:none' <td width=64 style='width:48pt'</td </tr <![endif] </table </body </html -- Tatiana "Joel" wrote: Post the query you got from the recorded macro. You can filter the SQL to get the dates within a range of dates. Perform a nd query while recording a macro and when you get to the 2nd menu (filter) set the 1s filter to after and put in the start date and then select a 2nd obx and use the filter before for the 2nd date. Then post the query results. You can use a connection to get all the obecjt between th e 2 dates. "Greenwind" wrote: Thank you so much for the advice. There is a small problem regarding changing the format of dates in Column A though. I only can search the date 31 Jan 88 but I cannot change it within MS query. Yes, in my last post, I thought I can change the dates. "It is feasible to open the database and retrieve the data one row at a time. Do you know how? Thanks! -- Tatiana "Joel" wrote: See below "Greenwind" wrote: Hi Joel, Im sorry for the confusion I caused. My data is in an Excel file called Excelhanding.xls. The format of it were distorted after copying and pasting. It should be: Column A Column B Column C Column D Column E Column F Time Debt Company Issuing date Year Arranged Debt 31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045 28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482 31 Mar 88 4.5711 A0004 30/09/2003 1990 ¦ ¦ A0004 01/04/2004 ¦ 31 Dec 88 3.0045 A0004 30/09/2005 ¦ ¦ ¦ A0005 31/12/1989 ¦ 31 Dec 89 3.0482 A0005 31/12/1990 ¦ 31 Dec 01 3.1124 ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ 31 Dec 05 4.5711 ¦ ¦ 2001 3.1124 ¦ ¦ ¦ ... ¦ ¦ 2005 ¦ ¦ ¦ 1988 ¦ ¦ ¦ ¦ A0005 31/12/2005 2001 ¦ 2005 ¦ Youre absolutely right.! For Column A, I only have monthly data from Jan 31, 1988 to Dec 31, 2005. It is feasible to open the database and retrieve the data one row at a time. I just think it will be a good exercise to learn SQL. After all, I always in a situation where I need to change Jan 31 88 to 31/12/1988 and to compare two columns. 1) Id like to format the data which already exists in the Excel worksheet. That said, I need to fill the Column F with the content of Column B within this Excelhandling.xls. Im reading two books on SQL Server 2005 Programming, a book on Excel and a book called Integrating Excel and Access, which also talked some basic Microsoft Visual Basic (VBA). However, after all this reading, Im still confused about whether I should use Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query (to write a program using the SQL window). I dont think I can use Macros though. ------------------------------------------------------------------------------------------- You don't need a server for SQL. the SQL is really the CommandText portion of the query. Dont learn SQL let Excel generate it for you. Start recording a macro from the worksheet menu - tools - Macro _record New Macro. Then perform a query by going to worksheet menu Data - Import External Data. Choose any of the query methods. ------------------------------------------------------------------------------------------- 2) The purpose of changing the format of Column A, is to be able to compare with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this period. Therefore 3.1124 in Column B is put the place in Column F. That is also the reason the date does not matter. As long as Dec 01 in Column A is correspond to 12/2001 in Column D, we can put this 3.1124 to the right place in Column F. I realize that I can do it manually but I think it will be a good exercise to learn SQL. After all, I always in a situation where I need to change Jan 31 88 to 31/12/1988 and to compare two columns. You can compare just the month and year ColA_Date = Range("A2") ColD_Date = Range("D2") if month(ColA_Date) = month(ColD_Date) and _ year(ColA_Date) = year(ColD_Date) then You can also force the date to the 1st of the month ColA_Date = Range("A2") FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1) You can also force the date to the last day of the month by going to 1st of next month and subtracting 1 and it even works if the date is in December. VBA thinks there are 13 months MyDate = "12/13/09" LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1 3) As for the content of Column A to D, it has already been filled with data. Im aware that it perhaps is too much to ask to let you write the whole program (to fill Column F) for me, but Im really stuck here. May I write some codes to let you see what problem do I have from time to time, please? I would write the entire code for you but your wouldn't learn as much as doing it yourself. I will help. Thank you so much for your time and input! -- Tatiana "Joel" wrote: I have lots of quetions but lets take it slow at first. Your error message is being cause because you are trying to modify a worksheet that is linked by a query to a database. You need to copy the data to a new sheet to be able to modify the data. The code below will automaticaly copy the data to an existing worksheet Sub CopySheet() Set Sourcesht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") Sourcesht.Cells.Copy Destination:=DestSht.Cells End Sub 1) My problem with your posting is I'm not sure is you need to query the database to get additional data or you are just formating the data that already exists on the worksheet. Instead of qerying the database you can actually open the database and retrieve the data one row at a time. 2) I also need futher explaination with your statment of dates "I have to change them to the same format". You don't mean formating the cells on the worksheet but make the dates the same time reference that same such as the last date of the month. 3) I think you should start by filling in the missing dates in columns A - D to make it easier to perform lookups. You can do this automatically with code. I think you want to attempt to do most of the work yourself with me advising? "Greenwind" wrote: I'm sorry for hijacking this question but somehow I just cannot post any new question here. My data is in an Excel file and I'd like to fill a column within this file. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Prevent function errors caused by cutting & paste or drag & drop | Excel Worksheet Functions | |||
| FORMULA ERRORS CAUSED BY SPACEBAR | Excel Worksheet Functions | |||
| Uninstall of mappoint has caused errors with excel | Excel Discussion (Misc queries) | |||
| Compile Errors with Different versions of Excel | Excel Discussion (Misc queries) | |||
| Excel 97 Macro Compile Errors | Excel Programming | |||
Threaded Mode
