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

S

SS

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
 
R

Ron Rosenfeld

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
Click to expand...

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
======================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

switching workbooks using variable and wilcard filename 8
Excellent code now a question of Feasibility 7
Custom toolbar button for search and replace 1
Delete all data after a symbol in a field 0
Create a chart from a table in word thats dynamic 0
Range headings 0
Extracting data from spreadsheet to a graph 1
Change a great number of words in word files, with a macro as short as possible. 5

Top