VLOOKUP in a macro??

[Guest]

What I'm trying to do with my macro is this. I have an input box where the
user will put in text, numbers, etc. The macro automatically puts that value
in the next empty row in coulmn A. But before it does that I want the macro
to search all the previous strings in column A to see if there is the same
value. And if there is I want it to prompt the user to another set of
questions.

The current macro looks like this and I'm trying to incorporate the above
with the entry1 string:

Public Sub getdata()
Dim nextrow As Long
Dim entry1 As String, entry2 As String, entry3 As String
Dim entry4 As String, entry5 As String

Do
nextrow = Range("A65536").End(xlUp).Row + 1

entry1 = InputBox("What is the HFC MAC?", "HFC")
If entry1 = "" Then Exit Sub

entry2 = InputBox("What kind of modem is it?")
If entry2 = "" Then entry2 = Cells(nextrow - 1, 2).Value

entry3 = InputBox("Where is the modem? Default is 'Shelved'")
If entry3 = "" Then entry3 = "Shevled"

entry4 = InputBox("What's the status of the modem: Renting, Purchased or
Pending. Default is 'Pending'")
If entry4 = "" Then entry4 = "Pending"

entry5 = InputBox("What is today's date?")
If entry5 = "" Then entry5 = Cells(nextrow - 1, 5).Value

Cells(nextrow, 1) = entry1
Cells(nextrow, 2) = entry2
Cells(nextrow, 3) = entry3
Cells(nextrow, 4) = entry4
Cells(nextrow, 5) = entry5
Loop

End Sub

 

[Guest]

Find would probably be the most appropriate function to use...

if not(colmns("A").find(What:=Entry1, LookAt:=xlValues, Lookin:= xlPart,
MatchCase:=False) is nothing) then exit sub

 

[Bob Phillips]

Public Sub getdata()
Dim nextrow As Long
Dim entry1 As String, entry2 As String, entry3 As String
Dim entry4 As String, entry5 As String

Do
Application.ScreenUpdating = False

nextrow = Range("A" & Rows.Count).End(xlUp).Row + 1

entry1 = InputBox("What is the HFC MAC?", "HFC")
If entry1 = "" Then Exit Sub

If IsError(Application.Match(entry1, Columns(1), 0)) Then
entry2 = InputBox("What kind of modem is it?")
If entry2 = "" Then entry2 = Cells(nextrow - 1, 2).Value

entry3 = InputBox("Where is the modem? Default is 'Shelved'", ,
"Shelved")

entry4 = InputBox("What's the status of the modem: Renting, " & _
"Purchased or Pending. Default is 'Pending'", ,
"Pending")

entry5 = InputBox("What is today's date?")
If entry5 = "" Then entry5 = Cells(nextrow - 1, 5).Value

Cells(nextrow, 1) = entry1
Cells(nextrow, 2) = entry2
Cells(nextrow, 3) = entry3
Cells(nextrow, 4) = entry4
Cells(nextrow, 5) = entry5
End If

Application.ScreenUpdating = True

Loop

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Lonnie M.]

Hi, you can use the countif function instead, which would be faster and
easier. It would look something like this:

If worksheetfunction.CountIf('Range', 'Criteria') > 0 Then ...

HTH--Lonnie M.

 

[Dave Peterson]

You have another reply at your post in .misc.

If you have to post to multiple newsgroups, please include all the headers in
the To: field and just send it once.