Check a 'Logsheet' for a match, before continuing a sub
 

Hi,
I want to run an operation, but, this may have already been done, so i
need to notify user of that and not run again.

Specifically:
I need to search the 'Entry Log' sheet (columns A and B) for the word
'Enzyme' in column A, and a specific six digit number in column B (the
number is already defined as variable req1). These two entries must
be found adjascent to each other, in order for the find/match to be
true.

Example:
req1 is currently 123456

So, in order for my macro to continue, I want to search the sheet
'Entry Log' for the word 'Enzyme' in column A and 123456 in column B
(adjascent). If it does not find, the macro continues. If it does
find that match, say in range A26:B26, then a message to user appears
that data has already been entered, etc... and then exits.

Note: The Entry Log sheet Column A will have either the word Enzyme,
or Strip, or Error. Column B will have many different six digit
numbers.

Thanks for any assistance,
Steve

Check a 'Logsheet' for a match, before continuing a sub
 

Add a third column containing the text in columns A and B.
"= A1 & B1" and fill down.
Do your search in the third column.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls in the free folder)




"SS"
wrote in message
...
Hi,
I want to run an operation, but, this may have already been done, so i
need to notify user of that and not run again.

Specifically:
I need to search the 'Entry Log' sheet (columns A and B) for the word
'Enzyme' in column A, and a specific six digit number in column B (the
number is already defined as variable req1). These two entries must
be found adjascent to each other, in order for the find/match to be
true.

Example:
req1 is currently 123456

So, in order for my macro to continue, I want to search the sheet
'Entry Log' for the word 'Enzyme' in column A and 123456 in column B
(adjascent). If it does not find, the macro continues. If it does
find that match, say in range A26:B26, then a message to user appears
that data has already been entered, etc... and then exits.

Note: The Entry Log sheet Column A will have either the word Enzyme,
or Strip, or Error. Column B will have many different six digit
numbers.

Thanks for any assistance,
Steve

Check a 'Logsheet' for a match, before continuing a sub
 

On Tue, 29 Nov 2011 10:52:42 -0800 (PST), SS wrote:

Hi,
I want to run an operation, but, this may have already been done, so i
need to notify user of that and not run again.

Specifically:
I need to search the 'Entry Log' sheet (columns A and B) for the word
'Enzyme' in column A, and a specific six digit number in column B (the
number is already defined as variable req1). These two entries must
be found adjascent to each other, in order for the find/match to be
true.

Example:
req1 is currently 123456

So, in order for my macro to continue, I want to search the sheet
'Entry Log' for the word 'Enzyme' in column A and 123456 in column B
(adjascent). If it does not find, the macro continues. If it does
find that match, say in range A26:B26, then a message to user appears
that data has already been entered, etc... and then exits.

Note: The Entry Log sheet Column A will have either the word Enzyme,
or Strip, or Error. Column B will have many different six digit
numbers.

Thanks for any assistance,
Steve

Here's an example of one way to do this. The matchEntryLog function will return TRUE if the match is present; and you can use that test result to decide what you want to do.

Beware that it will NOT match if there are extraneous spaces in the data.

==============================
Option Explicit
Sub TestMatch()
Dim req1 As Variant
Dim MatchWord As String
MatchWord = "Enzyme"
req1 = "123456"

Debug.Print matchEntryLog("EntryLog", MatchWord, req1)

End Sub
Function matchEntryLog(wsName As String, FirstMatch, SecondMatch) As Boolean
Dim ws As Worksheet
Dim rFirstCol As Range
Dim c As Range
Dim sFirstAddress As String
Set ws = Worksheets(wsName)
Set rFirstCol = ws.Range("A:A")

matchEntryLog = False
With rFirstCol
Set c = .Find(what:=FirstMatch, _
LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If Not c Is Nothing Then
sFirstAddress = c.Address
If c.Offset(columnoffset:=1).Text = SecondMatch Then
matchEntryLog = True
Exit Function
End If

Do
Set c = .FindNext(after:=c)
If c.Offset(columnoffset:=1).Text = SecondMatch Then
matchEntryLog = True
Exit Function
End If
Loop Until c.Address = sFirstAddress Or c Is Nothing
End If
End With

End Function
======================================

Check a 'Logsheet' for a match, before continuing a sub
 

On Nov 29, 7:53*pm, "Jim Cone" wrote:
Add a third column containing the text in columns A and B.
"= A1 & B1" and fill down.
Do your search in the third column.
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls in the free folder)

"SS"
wrote in ...



Hi,
I want to run an operation, but, this may have already been done, so i
need to notify user of that and not run again.

Specifically:
I need to search the 'Entry Log' sheet (columns A and B) for the word
'Enzyme' in column A, and a specific six digit number in column B (the
number is already defined as variable req1). *These two entries must
be found adjascent to each other, in order for the find/match to be
true.

Example:
req1 is currently 123456

So, in order for my macro to continue, I want to search the sheet
'Entry Log' for the word 'Enzyme' in column A and 123456 in column B
(adjascent). *If it does not find, the macro continues. *If it does
find that match, say in range A26:B26, then a message to user appears
that data has already been entered, etc... and then exits.

Note: The Entry Log sheet Column A will have either the word Enzyme,
or Strip, or Error. *Column B will have many different six digit
numbers.

Thanks for any assistance,
Steve- Hide quoted text -

- Show quoted text -

Well, you're a genious!!
Worked great, Thanks!!

Check a 'Logsheet' for a match, before continuing a sub
 

You are welcome.
'---
Jim Cone
Portland, Oregon USA



"SS"
wrote in message
...
On Nov 29, 7:53 pm, "Jim Cone"
wrote:

Add a third column containing the text in columns A and B.
"= A1 & B1" and fill down.
Do your search in the third column.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls in the free folder)



"SS"
wrote in ...
Hi,
I want to run an operation, but, this may have already been done, so i
need to notify user of that and not run again.

Specifically:
I need to search the 'Entry Log' sheet (columns A and B) for the word
'Enzyme' in column A, and a specific six digit number in column B (the
number is already defined as variable req1). These two entries must
be found adjascent to each other, in order for the find/match to be
true.

Example:
req1 is currently 123456

So, in order for my macro to continue, I want to search the sheet
'Entry Log' for the word 'Enzyme' in column A and 123456 in column B
(adjascent). If it does not find, the macro continues. If it does
find that match, say in range A26:B26, then a message to user appears
that data has already been entered, etc... and then exits.

Note: The Entry Log sheet Column A will have either the word Enzyme,
or Strip, or Error. Column B will have many different six digit
numbers.

Thanks for any assistance,
Steve- Hide quoted text -

- Show quoted text -

Well, you're a genious!!
Worked great, Thanks!!