VLOOKUP in a macro??

[chip_pyp]

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

[Dave Peterson]

Option Explicit

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

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

Do

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

res = Application.Match(entry1, Range("a:a"), 0)

If IsError(res) Then
'not already in the list
Exit Do 'the inside Do/Loop
Else
'keep asking
MsgBox "already there, try again!"
End If
Loop

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 = "Shelved" '<--typo!

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?", Default:=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


Sooner or later you may want to consider making a userform to get this info.
Then the user won't have so many ok's to click on.

Debra Dalgleish has some notes:
http://www.contextures.com/xlUserForm01.html

chip_pyp wrote:
>
> 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

--

Dave Peterson