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)

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)