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
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
Hey saban
Found this in a reply by mdmackillop so if it works he's the man to thank.
[vba]
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
[/VBA]
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
Last edited by saban; 06-29-2006 at 03:22 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.
Yeah i need to find only "zaseden"
thnx
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
The Find Method in Help is worth studying. (Header address here is assumed as K2)
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
sorry (I forgot vba style)
[VBA]
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
[/VBA]
Thnx guys I really appreciate this
but why do i get error when "zaseden is not found" error is at line
[VBA]Set c = Columns("K").Find(What:="Zaseden", after:=Header, LookIn:=xlValues, _
[/VBA]
[VBA]
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
[/VBA]
Thnx guys it works
stay cool
Saban please mark this thread as solved