PDA

View Full Version : Solved: Find value in column



saban
06-29-2006, 12:55 AM
I have values in K column which are "Prost" or "Zaseden" and now I want to check if zaseden exist in this column and if exist then Msgbox "Yes it exists"

thnx

cdbrown
06-29-2006, 02:01 AM
Hey saban

Found this in a reply by mdmackillop so if it works he's the man to thank.


Sub FindLookup()
Dim ThsBook As Workbook, c As Range
Application.ScreenUpdating = False
Set c = Columns("K").Find(What:="Zaseden")
If Not c Is Nothing Then
MsgBox "Yes it exists"
Exit Sub
End If
MsgBox "Zaseden not found"
Application.ScreenUpdating = True
End Sub

saban
06-29-2006, 03:07 AM
Actually the problem is that it finds value "Zasedenost" which is the header of column and then if not c is nothing... gives me always "yes it exists"

Actually it does not work it doesnt find value "zaseden" at all

any ideas
Thnx

cdbrown
06-29-2006, 03:43 AM
I see. It seems to search for that string of letters regarldess if there is text either side. When you removed the header of the column and run it again - are you saying there was zaseden in the column but it didn't find it because I can't reproduce that.

Need to get it to find zaseden and only zaseden. Sorry about that.

saban
06-29-2006, 04:49 AM
Yeah i need to find only "zaseden"

thnx

ALe
06-29-2006, 05:38 AM
use this

Sub FindLookup()
Dim ThsBook As Workbook, c As Range
Application.ScreenUpdating = False
Set c = Columns("K").Find(What:="Zaseden", LookAt:=xlWhole)
If Not c Is Nothing Then
MsgBox "Yes it exists"
Exit Sub
End If
MsgBox "Zaseden not found"
Application.ScreenUpdating = True
End Sub

mdmackillop
06-29-2006, 05:39 AM
The Find Method in Help is worth studying. (Header address here is assumed as K2)

Sub FindLookup()
Dim ThsBook As Workbook, c As Range, Header As Range
Set Header = [K2]
Application.ScreenUpdating = False
Set c = Columns("K").Find(What:="Zaseden", after:=Header, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing And Not c.Address = Header.Address Then
MsgBox "Yes it exists"
Exit Sub
End If
MsgBox "Zaseden not found"
Application.ScreenUpdating = True
End Sub

ALe
06-29-2006, 05:40 AM
sorry (I forgot vba style)

Sub FindLookup()
Dim ThsBook As Workbook, c As Range
Application.ScreenUpdating = False
Set c = Columns("K").Find(What:="Zaseden", LookAt:=xlWhole)
If Not c Is Nothing Then
MsgBox "Yes it exists"
Exit Sub
End If
MsgBox "Zaseden not found"
Application.ScreenUpdating = True
End Sub

saban
06-29-2006, 06:59 AM
Thnx guys I really appreciate this

but why do i get error when "zaseden is not found" error is at line
Set c = Columns("K").Find(What:="Zaseden", after:=Header, LookIn:=xlValues, _

ALe
06-29-2006, 07:30 AM
Sub FindLookup()
Dim ThsBook As Workbook, c As Range, Header As Range
Set Header = [K2]
Application.ScreenUpdating = False
On Error GoTo NotFound
Set c = Columns("K").Find(What:="Zaseden", after:=Header, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing And Not c.Address = Header.Address Then
MsgBox "Yes it exists"
Exit Sub
End If
NotFound:
MsgBox "Zaseden not found"
Application.ScreenUpdating = True
End Sub

saban
06-29-2006, 07:50 AM
Thnx guys it works

stay cool

ALe
06-29-2006, 07:55 AM
Saban please mark this thread as solved