Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Match problem
SalesDateRow = Application.WorksheetFunction.Match(clng(thisDate) ,
SalesRange, 0) InvDateRow = Application.WorksheetFunction.Match(clng(thisDate) , InvRange, 0) Usually works for me to exactly match a date. -- Regards, Tom Ogilvyi "Marcotte A" wrote in message ... I am using Application.Match in my macro to find the row number containing a particular date. On two spreadsheets (call them "Sales" and "Inventory"), I have (consecutive) dates in columns A and H. I need to identify the row numbers of a user-inputed date on each sheet (in different workbooks) and copy data from the row on "Sales" to the row on "Inventory". I have used similar code in another part of the macro to copy Production data from a 3rd workbook to the Inventory workbook and it works fine. Here is the code that doesn't work - below it is the code that does. Sub RunAddSales() Dim answer As Date answer = InputBox("Enter Date to add Sales data.") AddSales answer End Sub Sub AddSales(thisDate As Date) Dim wkbSales As Workbook Dim SalesRange As Range Dim InvRange As Range Dim SalesDateRow As Single Dim InvDateRow As Single Dim i As Integer Dim sht As Worksheet Set wkbSales = Workbooks("KS EDI Reported Sales.xls") Set SalesRange = wkbSales.Worksheets("001").Range("H:H") Set InvRange = ThisWorkbook.Worksheets("001").Range("A:A") *SalesDateRow = Application.WorksheetFunction.Match(thisDate, SalesRange, 0) InvDateRow = Application.WorksheetFunction.Match(thisDate, InvRange, 0) For Each sht In ThisWorkbook If Len(sht.Name) = 3 Then For i = 0 To 16 sht.Cells(InvDateRow, 3 + i * 12) = wkbSales.Worksheets(sht.Name).Cells _ (SalesDateRow, i + 20) Next i End If Next sht End Sub *Error is generated by this line. The following code works fine. Function FindDateRow(myDate As Single, ShtNum As String) As Integer Dim RowNum As Integer Dim myrange As Range Set myrange = ThisWorkbook.Worksheets(ShtNum).Range("A:A") RowNum = Application.WorksheetFunction.Match(myDate, myrange, 0) FindDateRow = RowNum End Function I'm thinking that the problem has something to do with variable type. In the first, i get a Date variable from an input box. In the second, I pass a Single to the function for the date. The Single passed to FindDateRow is the .Value of a cell (which is formatted as a date - m/d/yyyy) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Match problem
Thanks Tom, that seems to be working. (Now if I could just learn to write
code that's more efficient, it wouldn't take me so long to find out if it does in fact work.) "Tom Ogilvy" wrote: SalesDateRow = Application.WorksheetFunction.Match(clng(thisDate) , SalesRange, 0) InvDateRow = Application.WorksheetFunction.Match(clng(thisDate) , InvRange, 0) Usually works for me to exactly match a date. -- Regards, Tom Ogilvyi "Marcotte A" wrote in message ... I am using Application.Match in my macro to find the row number containing a particular date. On two spreadsheets (call them "Sales" and "Inventory"), I have (consecutive) dates in columns A and H. I need to identify the row numbers of a user-inputed date on each sheet (in different workbooks) and copy data from the row on "Sales" to the row on "Inventory". I have used similar code in another part of the macro to copy Production data from a 3rd workbook to the Inventory workbook and it works fine. Here is the code that doesn't work - below it is the code that does. Sub RunAddSales() Dim answer As Date answer = InputBox("Enter Date to add Sales data.") AddSales answer End Sub Sub AddSales(thisDate As Date) Dim wkbSales As Workbook Dim SalesRange As Range Dim InvRange As Range Dim SalesDateRow As Single Dim InvDateRow As Single Dim i As Integer Dim sht As Worksheet Set wkbSales = Workbooks("KS EDI Reported Sales.xls") Set SalesRange = wkbSales.Worksheets("001").Range("H:H") Set InvRange = ThisWorkbook.Worksheets("001").Range("A:A") *SalesDateRow = Application.WorksheetFunction.Match(thisDate, SalesRange, 0) InvDateRow = Application.WorksheetFunction.Match(thisDate, InvRange, 0) For Each sht In ThisWorkbook If Len(sht.Name) = 3 Then For i = 0 To 16 sht.Cells(InvDateRow, 3 + i * 12) = wkbSales.Worksheets(sht.Name).Cells _ (SalesDateRow, i + 20) Next i End If Next sht End Sub *Error is generated by this line. The following code works fine. Function FindDateRow(myDate As Single, ShtNum As String) As Integer Dim RowNum As Integer Dim myrange As Range Set myrange = ThisWorkbook.Worksheets(ShtNum).Range("A:A") RowNum = Application.WorksheetFunction.Match(myDate, myrange, 0) FindDateRow = RowNum End Function I'm thinking that the problem has something to do with variable type. In the first, i get a Date variable from an input box. In the second, I pass a Single to the function for the date. The Single passed to FindDateRow is the .Value of a cell (which is formatted as a date - m/d/yyyy) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
application.match and value problem | Excel Discussion (Misc queries) | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming |