i have a worksheet called mulogs
first column A has dates and remaining have prices ... based on price entered i want it to check when the next date for close price is for the item. but it keeps giving #value when i use offset(1,0) for the upperval...but code works fine if i remove that and use simple c.value for the upperval just cant figure out y it doesnt work when i put in the offset code for upperval
Function findnextdt(chktype As String, chkprice As String, chkdt As Date) As String
Dim result
Dim colprefix, colstart, col, found, upperval, lowerval
Dim newdt
found = 0
Select Case chktype
Case "BOOK"
colprefix = "B"
colstart = "B2"
col = -1
Case "SHOES"
colprefix = "C"
colstart = "C2"
col = -2
Case "CAPS"
colprefix = "D"
colstart = "D2"
col = -3
end select
For Each c In Worksheets("mulogs").Range(colstart, Worksheets("mulogs").Cells(Worksheets("mulogs").Rows.count, colprefix).End(xlUp))
upperval = Replace(c.Offset(1, 0).Value, " Rx", "") + 3
lowerval = Replace(c.Value, " Rx", "")
If found = 0 Then
If chkprice < upperval And chkprice >= lowerval Then
newdt = c.Offset(0, col).Value
If chkdt < newdt Then
result = upperval
found = 1
End If
End If
End If
Next c
findnextdt = result
Bookmarks