Solved: Find value in column

[saban]

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]

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]

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]

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]

Yeah i need to find only "zaseden"

thnx

[ALe]

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]

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]

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]

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]

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]

Thnx guys it works

stay cool

[ALe]

Saban please mark this thread as solved