possible if function??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chip_pyp - 23 Mar 2006 23:51 GMT

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

Do
nextrow = Range("A65536").End(xlUp).Row + 1
entry1 = InputBox("What is the HFC MAC?")
If entry1 = "" Then Exit Sub
entry2 = InputBox("What kind of modem is it?")
If entry2 = "" Then Exit Sub
entry3 = InputBox("Where is the modem?")
If entry3 = "" Then entry3 = "Shevled"
entry4 = InputBox("What's the status of the modem: Renting, Purchased or
Pending")
If entry4 = "" Then entry4 = "Pending"
Cells(nextrow, 1) = entry1
Cells(nextrow, 2) = entry2
Cells(nextrow, 3) = entry3
Cells(nextrow, 4) = entry4
Loop

End Sub

thats what the macro looks like...after the user inputs the adata collected
for entry1 i want the macro to search the previous entries for that one then
promt other questions but if its not in there then is will insert the data to
the next empty row

Reply to this Message

tony h - 24 Mar 2006 00:11 GMT

A bit more clarity about what you want tot do would help

Signature

tony h

Reply to this Message

chip_pyp - 24 Mar 2006 17:57 GMT

basically what i'm looking for in this part is when the user inputs data
prompted by the first input box (entry1) i want the macro to take that user
input and compare it against previous entries in the same column, for example
user inputs 458 in for entry1, the macro will then search for 458 in the same
column and if its found it will then prompt you to change the data next to it
if needed, if it is not found then it will continue on wiht the rest of the
macro...i have no ide aif that makes any sense to you but it does to me :-P
thanks a million!

Reply to this Message

gimme_this_gimme_that@yahoo.com - 29 Mar 2006 06:02 GMT

'might this work?
If ActvieWorkbook.Date1904 Then
'do Apple stuff
Else
'do PC stuff
End If

Reply to this Message

idyllicabyss@googlemail.com - 24 Mar 2006 01:11 GMT

You may want to consider using a UserForm to collect all this data.
You can use a ComboBox for "What is the HFC MAC?" which allows the user
to choose from a predefined list (based on previously entered data) or
enables a textbox for entry if "Other..." is selected in the Combo.
ComboBoxes may also be suitable for your other fields.

For your existing code, you may consider revising the input prompts to;
while entry2 = ""
entry2 = InputBox "What kind of modem is it?"
wend
and so on for the other inputs

(I think my while/wend syntax is correct) This will avoid dumping the
user out of the procedure if they double press ENTER and inadvertantly
send an empty string.

Reply to this Message

tony h - 29 Mar 2006 22:59 GMT

try

Public Sub getdata()
Dim nextrow As Long
Dim entry1 As String, entry2 As String, entry3 As String
Dim entry4 As String
Dim rng As Range, rngF As Range

Do
nextrow = Range("A65536").End(xlUp).Row + 1
entry1 = InputBox("What is the HFC MAC?")
If entry1 = "" Then Exit Sub
Set rngF = Range("A:A").Find(entry1)
If rngF Is Nothing Then
entry2 = InputBox("What kind of modem is it?")
If entry2 = "" Then Exit Sub
entry3 = InputBox("Where is the modem?")
If entry3 = "" Then entry3 = "Shevled"
entry4 = InputBox("What's the status of the modem: Renting
Purchased or Pending ")
If entry4 = "" Then entry4 = "Pending"
Else
entry2 = InputBox("What kind of modem is it?", , Cells(nextrow
2))
If entry2 = "" Then Exit Sub
entry3 = InputBox("Where is the modem?", , Cells(nextrow, 3))
If entry3 = "" Then entry3 = "Shevled"
entry4 = InputBox("What's the status of the modem: Renting
Purchased or Pending ", , Cells(nextrow, 4))
If entry4 = "" Then entry4 = "Pending"

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

End Sub

regard

Reply to this Message