Using InputBox entry with .find 1

[THarte]

XL2000

In my code I am getting data from an input box (Policy Number) and in a later routine I search all the records for specific policy numbers to update records. If I have multiple polcy numbers in a column from top to bottom say 12345, 1234, 123 and the User enters 123 in the Input Box I am getting the 12345 (first record with 123 in it) returned.

I cannot figure out the code to get the Exact matching record.

Here is the code I have:
Do
With worksheets(2).Range("J:J", "L:L&quot
sPolNum = InputBox(Prompt:="Enter Policy Number or Last Name:&quot
Sheets(2).Select
Set oPolNum = .Find(sPolNum)
If Not oPolNum Is Nothing Then
Sfound = oPolNum.Address
If Range(Sfound).Offset(0, 22).Value = "" Then
Sheets(2).Range("AZ1&quot.Value = Sfound
Range(Sfound).EntireRow.Copy
Sheets(3).Select
Cells(65536, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Cells(65536, 1).End(xlUp).Offset(1, 0).Select
Else: sPolNum = InputBox(Prompt:="There are no active Calls with the requested Policy Number. Please Try Again:&quot
End If
End If
End With


Any help is appreciated.

 

[rmikesmith]

THarte,

I am responding to this post as well as your earlier post regarding the same procedure. Try using the following, which is a rather extensive re-write of the code you posted. I believe it will address both of the problems you are having. At least it should be a starting point which you can modify, if needed.

Sub UpdatePolicy()
Dim sPolNum As String
Dim wks2 As Worksheet
Dim wks3 As Worksheet
Dim Result As Variant
Dim LastEntryAddress As String
Dim LookupRange As String
Dim MatchRange As Range
Dim SaveRowNum As Long

Set wks2 = Worksheets("Sheet2")
Set wks3 = Worksheets("Sheet3")

Do
  sPolNum = InputBox(Prompt:="Enter Policy Number or Last Name:")
  If sPolNum = "" Then Exit Sub

  If IsNumeric(sPolNum) Then
    LastEntryAddress = ActiveSheet.Cells(65536, 2).End(xlUp).Address
    LookupRange = "$B$1:" & LastEntryAddress
    Set MatchRange = wks2.Range(LookupRange)
  Else
    LastEntryAddress = ActiveSheet.Cells(65536, 3).End(xlUp).Address
    LookupRange = "$C$1:" & LastEntryAddress
    Set MatchRange = wks2.Range(LookupRange)
  End If
  Result = Application.Match(sPolNum, MatchRange, 0)
  sPolNum = ""
  If Not IsError(Result) Then
    wks2.Rows(Result).Copy
    wks3.Select
    Cells(65536, 1).End(xlUp).Offset(1, 0).Select
    SaveRowNum = Selection.Row
    ActiveSheet.Paste
   ' Manipulate "Sheet3" cells here

   'Replace "Sheet2" Row with updates
    wks3.Rows(SaveRowNum).Copy
    wks2.Cells(Result, 1).PasteSpecial
    Application.CutCopyMode = False
  Else
    MsgBox "No match found.", vbOKOnly + vbExclamation, "Select Policy Number/Name"
    '*** or ***
    Result = InputBox(Prompt:="No match found.  Try again:")
  End If
Loop Until sPolNum = ""

End Sub

Reply as to whether this helped or not.

M. Smith

 

[THarte]

Thanks Mike.

Couple things

I am getting 1004 error on this Line
Set MatchRange = wks2.Range(LookupRange)
Says Method 'Range'of object '_worksheet'failed

Will the SaveRowNum code you have save the address even after you exit the sub? I exit the sub to manipulate the data in another userform then call the address to replace with the new data. That is why I was writing it to a cell.

 

[THarte]

Mike

Also the Policy Numbers can be alpha numeric.

Lastly I write the found row to a separate sheet and then reference the cells on that sheet to fill the userform referenced in the last post, maipulate the data in the userform, write over the row on the separate sheet and then replace the original row in the Orginal Sheet. I am now sure where the "SaveRow" saves the values so I can refer to them to fill the userform for data manipulation. Maybe my original post should have had more info. Sorry.

 

[rmikesmith]

THarte,

The 1004 error in this context usually means an invalid range is being referenced. Single-step through the procedure (or when the error dialog appears, select Debug) and look at the value of LookupRange.

I've modified the code to handle alphanumeric policy numbers. Since the Match function will only accept a single column (or row) we have to search separately for a number or name and can no longer use the IsNumeric function to choose which column to search. See below.

SaveRowNum, as currently declared, will retain its value only as long as procedure UpdatePolicy is running. If you want to be able to reference this in a subsequent procedure you need to declare it a global variable (in a code module, outside of procedures or functions).

It is important to note that the Match worksheet function, when called from VBA, will not properly match a supplied string (i.e. sPolNum) with cell contents interpreted as a number. I don't know why this is so, as this isn't an issue when used as a worksheet function. To make it work, Format as 'Text' the columns to be searched. If the column already contains data you cannot simply change the formatting. Follow these steps: 1) Cut or copy data to an unused column; 2) Format the original column as Text; 3) Select the moved data then choose Copy; 4) Use Paste Special, selecting Values only, to return the data to the original column.

The following code replaces everything between

If sPolNum = "" Then Exit Sub

and

sPolNum = ""

in my previous post.

LastEntryAddress = ActiveSheet.Cells(65536, 2).End(xlUp).Address
LookupRange = "$B$1:" & LastEntryAddress
Set MatchRange = wks2.Range(LookupRange)
Result = Application.Match(vPol, MatchRange, 0)
If IsError(Result) Then
  LastEntryAddress = ActiveSheet.Cells(65536, 3).End(xlUp).Address
  LookupRange = "$C$1:" & LastEntryAddress
  Set MatchRange = wks2.Range(LookupRange)
  Result = Application.Match(sPolNum, MatchRange, 0)
End If

Let me know if you have any other difficulties or questions.

Mike

 

[THarte]

What is vPol? It is getting a compile error on this saying it is not defined.

On the global variable I add a Class Module and put the following under General/Declarations....Dim SaveRowNum As Long

Is that the correct way?

Sorry for all the dumb questions but I am new to this.

 

[THarte]

Rather than Dim SaveRowNum As Long, I meant to say....Dim MatchRange As Range in the Class Module

 

[rmikesmith]

THarte,

vPol should be sPolNum. I was trying something out and in my haste forgot to remove it before posting. Sorry for any confusion.

Is your procedure in a Class Module? You can Dim the MatchRange variable as you've stated and the procedures in the class will be able to reference this. If you need direct access by procedures outside the class (i.e. in a code module), use the Public keyword, instead. If your procedures are in a code module, then Dim MatchRange at the beginning of the code module.

Regards,
Mike

 

[THarte]

Getting "No Match" on any policy number entered.

Here is the code I have in there (Column J houses the policy numbers Starting on Row2 (row 1 is "Policy Number" title; Colum L houses the Last names Starting in Row2)

I changed your A;B to J:L and Activesheet(65536, 2:3) to 9:11. The only other thing I think I changed was calling Userform3 at the end and that is where I manipulate the data. What did I do wrong?


Private Sub CommandButton2_Click()
Dim sPolNum As String
Dim wks2 As Worksheet
Dim wks3 As Worksheet
Dim Result As Variant
Dim LastEntryAddress As String
Dim LookupRange As String
Dim MatchRange As Range
Dim SaveRowNum As Long

Set wks2 = worksheets("Sheet2&quot
Set wks3 = worksheets("Sheet3&quot

Do
sPolNum = InputBox(Prompt:="Enter Policy Number or Last Name:&quot
If sPolNum = "" Then Exit Sub
LastEntryAddress = ActiveSheet.Cells(65536, 9).End(xlUp).Address
LookupRange = "$J$1:" & LastEntryAddress
Set MatchRange = wks2.Range(LookupRange)
Result = Application.Match(sPolNum, MatchRange, 0)

If IsError(Result) Then
LastEntryAddress = ActiveSheet.Cells(65536, 11).End(xlUp).Address
LookupRange = "$L$1:" & LastEntryAddress
Set MatchRange = wks2.Range(LookupRange)
Result = Application.Match(sPolNum, MatchRange, 0)
End If

sPolNum = ""
If Not IsError(Result) Then
wks2.Rows(Result).Copy
wks3.Select
Cells(65536, 1).End(xlUp).Offset(2, 0).Select
SaveRowNum = Selection.Row
ActiveSheet.Paste
UserForm2.Hide
UserForm3.Show
Else
MsgBox "No match found.", vbOKOnly + vbExclamation, "Select Policy Number/Name"
'*** or ***
'Result = InputBox(Prompt:="No match found. Try again:&quot
End If
Loop Until sPolNum = ""


End Sub

 

[rmikesmith]

THarte,

It looks like you are using the wrong indexes in the Cells construct: Column J equates to an index of 10 and Column L to 12 (the Cells indexing is 1-based).

Also be sure to format Columns J & L as 'Text' as mentioned in my previous post (if you don't, you will also get "No Match" message).


Mike

 

[THarte]

Thanks Mike

Everything seems to be working except writing back the New record over the old. All my code is in Forms (are these Code modules?). So the code you have helped me with is in form 2, when the record is found in form 2 then form 3 is called and populated with the record. It is in Form 3 that I write the new (manipulated record) over the old in wks2.

In the code you wrote:

'Replace "Sheet2" Row with updates
wks3.Rows(SaveRowNum).Copy
wks2.Cells(Result, 1).PasteSpecial
Application.CutCopyMode = False

It looks like I need to have SaveRowNum and Result from Form 2 set as global or somehow made available to form 3 when I go to paste the New Record. There is no reference to Global in Help and I could not make heads or tails of the Class Module purpose. Can I put all 3 forms in a class module and make SaveRow and Result Public? I guess I am struggling with what can and cannot be done.

 

[rmikesmith]

THarte,

A quick primer: Most "general-purpose" procedures and functions are stored in Code Modules. You can encapsulate objects & procedures in a Class Module. If well-written, these can serve as re-usable "black-boxes" where only the relevant properties and methods are exposed to other code. Forms have associated event procedures stored separate from code or class modules.

I would leave your forms as they are. When I mentioned making SaveRowNum (and now also Result) a global variable, I meant declaring it at the beginning of a Code Module, outside of any other procedures or functions. In order for your form procedures to "see" these variables, declare them using the Public keyword. If you don't already have a Code module, insert a new one from the VBA editor. You will probably see "Option Explicit" at the top of the module page. Move the cursor below this line and type

Public SaveRowNum As Long

, and

Public Result As Variant

. That should do it. The other option is to store these values in a worksheet cell for later retrieval by the form procedures.

Let me know how you make out.

Mike

 

[THarte]

Works beautifully Mike. Thanks a ton for your help and patience.

I have one column in every record that is "Completion Date" If I only want to sort on Records where this is Empty... what is the best way to accomplish this?

I have tried "If Isempty Then" statements, "Do While Statements", "If, Then Else statements" for the last hour and I keep coming up blank.

Basically when the old record is pasted to sheet3 the cell will always be "AF2" so I was trying to do the "If" after it had written to sheet3 and before it populated the userform. I tried to do it before the old record was pasted to sheet 3 using Result (which returns a row number) and I could not figure out the context to do an "If Sheet(3).(Result:column32)Then" type statement.

If the completion date cell is populated I just want to loop back to the beginning of the sub.

Is experience and asking questions the best way to learn this stuff? I have Walkenbach's book on PowerProgramming but I can't find most of this stuff in there. Any other resources you would suggest?

 

[rmikesmith]

THarte,

I'm not sure I completely understand the details of what you are trying to do, but the following line should work to determine whether the Completion Date cell is empty:

If IsEmpty(wks2.Cells(Result, 32)) Then 'Do something

If this isn't what you're looking for post back with more detail.

As to your question about learning this stuff -- Everyone has their own best way of learning. For me, I like to use a combination of private study and hands-on programming. For instance, I also have John Walkenbach's book (can't go wrong there) and I would read it as I would a textbook, away from the computer, say before bed. This gives you some fundamentals, so when you start programming you have a general idea of what to do. I also keep the book handy, as a reference, because inevitably you will need a custom function or snippet of code that you sort of remember but... The other side of the coin is simply getting in there and writing code, and when it fails (and it will fail), consulting the online help, reference books, forums, etc. Few of us use all facets of the language often enough to have every object, function and procedure at our fingertips. I have spent more than my fair share of time on trial and error coding! Don't hesitate to ask for help.

Regards,
Mike

 

[THarte]

That is the first line. I would want an Else statement statement that starts again at the beginning of the Sub if Completion Date is not empty (Not allowing Users to edit and change Completed Records). I tried a couple iterations of calling the beginning of the Sub (Sub CommandButton2_Click()) but it looks like one cannot call the sub you are in or I did not have the right syntax.

That is why I was trying the Do...while loops that were bombing. Trying to get to the beginning of the sub.

Thanks for your take on learning more.

 

[rmikesmith]

THarte,

Ok, I think I see what you are trying to do. I've reproduced the Button2 Click procedure, highlighting the changes:

Private Sub CommandButton2_Click()
Dim sPolNum As String
Dim wks2 As Worksheet
Dim wks3 As Worksheet
'  Dim Result As Variant    Now declared globally
Dim LastEntryAddress As String
Dim LookupRange As String
Dim MatchRange As Range
'  Dim SaveRowNum As Long     Now declared globally

   Set wks2 = worksheets("Sheet2")
   Set wks3 = worksheets("Sheet3")

   Do
     sPolNum = InputBox(Prompt:="Enter Policy Number or Last  Name:")
     If sPolNum = "" Then Exit Sub
     LastEntryAddress = ActiveSheet.Cells(65536, 10).End(xlUp).Address
     LookupRange = "$J$1:" & LastEntryAddress
     Set MatchRange = wks2.Range(LookupRange)
     Result = Application.Match(sPolNum, MatchRange, 0)

     If IsError(Result) Then
       LastEntryAddress = ActiveSheet.Cells(65536, 12).End(xlUp).Address
       LookupRange = "$L$1:" & LastEntryAddress
       Set MatchRange = wks2.Range(LookupRange)
       Result = Application.Match(sPolNum, MatchRange, 0)
     End If
                      
     sPolNum = ""
     If Not IsError(Result) Then

If Not IsEmpty(wks2.Cells(Result, 32))

wks2.Rows(Result).Copy
         wks3.Select
         Cells(65536, 1).End(xlUp).Offset(2, 0).Select
         SaveRowNum = Selection.Row
         ActiveSheet.Paste
         UserForm2.Hide
         UserForm3.Show[code]
               [COLOR=red][code]Else
         Msgbox "Completed records cannot be changed!", vbExclamation + vbOkOnly, "Select Policy Number/Name"
       End If

[/color]

Else
        MsgBox "No match found.", vbOKOnly + vbExclamation, "Select
            Policy Number/Name"
        '*** or ***
         'Result = InputBox(Prompt:="No match found.  Try again:")
      End If
   Loop Until sPolNum = ""

End Sub

Post back with results.

Mike

 

[THarte]

Does not like: If Not IsEmpty(wks2.Cells(Result, 32))

Gives "Compile Error: Syntax Error"

It was giving me the same error when I was messing around with this kind of statement the other day.

 

[rmikesmith]

THarte,

I'm stumped on this one. I can't even force my code to give me that error, no matter what I put inside the parentheses. If you have the CD handy that came with Walkenbach's book, try to run "range selections.xls" located in the "chap11" folder. If not drop the following excerpted procedure into a code module, fill a few worksheet cells in a single row, select the leftmost cell, then try to run the procedure.

Sub ActivateNextBlankToRight()
    ActiveCell.Offset(0, 1).Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).Select
    Loop
End Sub

Mike

 

[THarte]

I got the syntax error to go away. There was a missing "Then" after the If Not IsEmpty. Right before that statement I put in a msgbox to show me the Result value and it is getting the right row for the policy number requested but it will still pull a record with the completion date populated into userform3.

If I take the Not out of If No IsEmpty then every record searched regardless of whether the Completion date is populated returns the "Completed Record Cannot be changed"

If I change the column offset from 32 to 33 (empty column) all records return "Completed Record Cannot be changed"

Wacky...

 

[rmikesmith]

THarte,

I apologize. My logic isn't so logical. The Not shouldn't be there (although I can't account for the behavior reported in your second paragraph). However, lets make this a more definitive test, since as it's designed now, an inadvertant space entered into the Completion Date column will prevent the record from being edited. So, change the line to read:

If Not IsDate(wks2.Cells(Result, 32)) Then

Also, Move sPolNum to just below this line (since this is our loop control and is set to an empty string before the test for editable record; result: the loop will always be exited after one pass).

Try these two changes.

Mike