Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding row
The following works as it is written, but I can not figure out how to get
check the value in the column to the left of the find. something like Set x = Offset(0,1). Range("sales" & CD).Find(CP) But everything I have tried has returned an error. Function Checkpost(CP As Date, CD As String) Dim x With Worksheets("sales " & CD) Set x = Range("sales" & CD).Find(CP) End With If x Is Nothing Then Checkpost = True Else Checkpost = False End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding row
x.offset(0,-1) should work. Put this into the ELSE part of the if statement.
"treasuresflemar" wrote: The following works as it is written, but I can not figure out how to get check the value in the column to the left of the find. something like Set x = Offset(0,1). Range("sales" & CD).Find(CP) But everything I have tried has returned an error. Function Checkpost(CP As Date, CD As String) Dim x With Worksheets("sales " & CD) Set x = Range("sales" & CD).Find(CP) End With If x Is Nothing Then Checkpost = True Else Checkpost = False End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding row
Set x = Range("sales" & CD).Find(CP).offset(0,-1)
"treasuresflemar" skrev: The following works as it is written, but I can not figure out how to get check the value in the column to the left of the find. something like Set x = Offset(0,1). Range("sales" & CD).Find(CP) But everything I have tried has returned an error. Function Checkpost(CP As Date, CD As String) Dim x With Worksheets("sales " & CD) Set x = Range("sales" & CD).Find(CP) End With If x Is Nothing Then Checkpost = True Else Checkpost = False End If End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding row
You also need to check if the cellis in column A,otherwise, you could get an
error. "treasuresflemar" wrote: The following works as it is written, but I can not figure out how to get check the value in the column to the left of the find. something like Set x = Offset(0,1). Range("sales" & CD).Find(CP) But everything I have tried has returned an error. Function Checkpost(CP As Date, CD As String) Dim x With Worksheets("sales " & CD) Set x = Range("sales" & CD).Find(CP) End With If x Is Nothing Then Checkpost = True Else Checkpost = False End If End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding row
Sorry, I explained that all wrong.
CP will always be found in column "A" There will always be only one occurrence of CP in the range. It will always be found. Need to see if the sum of columns "B" through "L" in the row CP is found in 0 if so then checkpost = false What I need x to do is return the row number and I can handle the rest, I think. Thanks Carl Function Checkpost(CP As Date, CD As String) 'CP is the date that is being posted 'CD is the Year being posted which is part 'of the named range 'Checkpost returns True if no posting has been made 'to the posting date Dim x With Worksheets("sales " & CD) 'sets the sheet the data is on for the year invloved Set x = Range("sales" & CD).Find(CP).Offset(0, 1) Set x = Range("sales" & CD).Find(CP) 'selects the correct range End With If x Is Nothing Then Checkpost = True 'Nothing posted to that day Else Checkpost = False 'A posting to that date has been done End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding second value? | Excel Worksheet Functions | |||
Finding the last one | Excel Programming | |||
finding a tab name | Excel Programming | |||
finding the last value | Excel Discussion (Misc queries) | |||
vba..finding first non-zero value in a row | Excel Programming |