VB script help to find columns and change...

RS,

I added a Boolean variable and reused two of the existing
variables to add a final MsgBox to the code.
Also, I added a check after all the input boxes are shown to
make sure at least something was entered in the boxes.
'-----------------------
Sub changedhrcode()
Dim lastRow As Long
Dim cell As Excel.Range
Dim txtDate As String
Dim txtName As String
Dim txtHR As String
Dim blnFound As Boolean

lastRow = Range("B65536").End(xlUp).Row
txtDate = InputBox("Enter Date You want the records searched:", "Enter Date ")
txtName = InputBox("Enter Employee Name to search for:", "Enter Name")
txtHR = InputBox("Enter HR Code to replace old:", "Enter HR Code")

If Len(txtDate) * Len(txtName) * Len(txtHR) = 0 Then
MsgBox "Not all required data was entered. ", vbExclamation, "RS did this"
Exit Sub
End If

For Each cell In Range("B1:B" & lastRow)
If cell.Value = txtDate And cell.Offset(0, 1).Value = txtName Then
cell.Offset(0, 2).Value = txtHR
blnFound = True
End If
Next cell

If blnFound Then
lastRow = vbInformation
txtName = "Records found, transaction completed. "
Else
lastRow = vbExclamation
txtName = "No records found. "
End If
MsgBox txtName, lastRow, "RS did this"
End Sub
'---------------------------

Jim Cone
San Francisco, USA



"RompStar" <rmiecznik@comcast.net> wrote in message
news:1124144885.860096.271850@g43g2000cwa.googlegroups.com...
One more question:
The script works great, I am intergrating it with a network script
which I will post in due time for feedback :- )
The one last thing that I can think of is this, I want the user that
runs this script to get some kind of a msg box at the end...
So if the requests records were found spit out a msg box "Record found,
transaction completed", and if the records weren't found msg box "
something else "info not found" or whatever, what would be the best way
to do that ?

Thanks a lot, this helped me, I got it like this:


Sub changedhrcode()

Dim lastrow, cell, cnt
Dim txtDate As Date
Dim txtName As String, txtDept As String


cnt = 0
lastrow = Range("B65536").End(xlUp).Row
txtDate = InputBox("Enter Date You want the records searched for:",
"Enter Date")
txtName = InputBox("Enter Employee Name to search for:", "Enter Name")
txtDept = InputBox("Enter HR Code to replace old:", "Enter Dept")
For Each cell In Range("B1:B" & lastrow)
If cell.Value = txtDate And cell.Offset(0, 1).Value = txtName Then
cell.Offset(0, 2).Value = txtDept
cnt = cnt + 1
End If
Next cell
If cnt > 0 Then
MsgBox "Number of records updated: " & cnt
Else
MsgBox "Info Not Found"
End If

End Sub

Also what I wanted to do is this:

Basically that Upper Case didn't Matter, as long as the name matches,
and also the order in which it was
entered didnt' matter...

For example:

Raymond Stone

or

Stone Raymond

and it wouldn matter if it was Raymond or raymond ? I know LCase I
think is the function to use, just never used it before.