Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP in a macro??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP in a macro??
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 -- HTH... Jim Thomlinson "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP in a macro??
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) "chip_pyp" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP in a macro??
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP in a macro??
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Macro? | Excel Worksheet Functions | |||
Macro - Vlookup | Excel Discussion (Misc queries) | |||
vlookup macro | New Users to Excel | |||
VBA Macro for VLOOKUP | Excel Programming | |||
Need a macro to do what this VLOOKUP does | Excel Programming |