Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.find not finding corect value
Why would .find return a bad match?
in the named range , first column there is a list of dates from 1/1/2007 to 12/31/2007. all varables are getting correct data. call sub yr =2007 dt = 1/1/2007 First column in first row = 1/1/2007 but find returns rc = 306 which is 11/1/2007 Sub PostSales(yr As String) 'Module for posting current sales Dim rn As String ' named range holder Dim rc As String ' row count holder Dim ry As String 'worksheet name holder Dim dt As Date 'date of posting Application.ScreenUpdating = False ry = "sales " & yr 'set worksheet rn = "sales" & yr 'set named range dt = Worksheets("daily").Range("b2").Value 'gets posting date With Range(rn) ' range to use '=========================== .Select 'here for testing only remove after testing '=========================== rc = .Find(dt).Rows.Row 'get row number of match End With With Worksheets(ry) 'posts actual data to sales sheet .Range("a" & rc).Value = Worksheets("daily").Range("b2").Value .Range("b" & rc).Value = Worksheets("daily").Range("b3").Value .Range("c" & rc).Value = Worksheets("daily").Range("b4").Value .Range("d" & rc).Value = Worksheets("daily").Range("b5").Value .Range("e" & rc).Value = Worksheets("daily").Range("b6").Value .Range("f" & rc).Value = Worksheets("daily").Range("b8").Value .Range("g" & rc).Value = Worksheets("daily").Range("b9").Value .Range("h" & rc).Value = Worksheets("daily").Range("d7").Value .Range("i" & rc).Value = Worksheets("daily").Range("b27").Value .Range("j" & rc).Value = Worksheets("daily").Range("e16").Value .Range("k" & rc).Value = Worksheets("daily").Range("e46").Value .Range("l" & rc).Value = Worksheets("daily").Range("e21").Value End With Worksheets("daily").Range("b2").Value = dt + 1 'set date on entry screen to next day Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.find not finding corect value
Never mind found the problem
did not specify LookIn:=xlValues, lookat:=xlWhole Worked correctly after that "treasuresflemar" wrote in message ... Why would .find return a bad match? in the named range , first column there is a list of dates from 1/1/2007 to 12/31/2007. all varables are getting correct data. call sub yr =2007 dt = 1/1/2007 First column in first row = 1/1/2007 but find returns rc = 306 which is 11/1/2007 Sub PostSales(yr As String) 'Module for posting current sales Dim rn As String ' named range holder Dim rc As String ' row count holder Dim ry As String 'worksheet name holder Dim dt As Date 'date of posting Application.ScreenUpdating = False ry = "sales " & yr 'set worksheet rn = "sales" & yr 'set named range dt = Worksheets("daily").Range("b2").Value 'gets posting date With Range(rn) ' range to use '=========================== .Select 'here for testing only remove after testing '=========================== rc = .Find(dt).Rows.Row 'get row number of match End With With Worksheets(ry) 'posts actual data to sales sheet .Range("a" & rc).Value = Worksheets("daily").Range("b2").Value .Range("b" & rc).Value = Worksheets("daily").Range("b3").Value .Range("c" & rc).Value = Worksheets("daily").Range("b4").Value .Range("d" & rc).Value = Worksheets("daily").Range("b5").Value .Range("e" & rc).Value = Worksheets("daily").Range("b6").Value .Range("f" & rc).Value = Worksheets("daily").Range("b8").Value .Range("g" & rc).Value = Worksheets("daily").Range("b9").Value .Range("h" & rc).Value = Worksheets("daily").Range("d7").Value .Range("i" & rc).Value = Worksheets("daily").Range("b27").Value .Range("j" & rc).Value = Worksheets("daily").Range("e16").Value .Range("k" & rc).Value = Worksheets("daily").Range("e46").Value .Range("l" & rc).Value = Worksheets("daily").Range("e21").Value End With Worksheets("daily").Range("b2").Value = dt + 1 'set date on entry screen to next day Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Find not finding all dupes | Excel Discussion (Misc queries) | |||
Finding row number with .find() | Excel Programming | |||
Find/Replace not Finding | Excel Discussion (Misc queries) | |||
How do I get the Excel Find function to keep finding what I want? | Excel Discussion (Misc queries) | |||
Find method - finding multiple values | Excel Programming |