VB script help to find columns and change...

[RompStar]

Ok, I have 4 columns:

Department, Date, Employee Name, Daily HR attendance code

so

A, B, C, D

Headers are on row 1, everything below that is data...

So what I wanted to be able to do, is change the HR code with a script,
incase managers make a mistake.

So, first first the Date from B, then in Offset find the right name to
match the date, and then if those two match change the HR code using
offset on the same row.

starts here -------------

Sub changedhrcode()

Dim lastrow As Long
Dim cell As Range
Dim txtDate As Date
Dim txtName As String
Dim txtHR As String

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")

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

End Sub

So I highlighted a row that I wanted to test this script on, I enter
the Date, then the Name, and then the new HR code that was supposed to
replace it, but it doesn't replace it, so I am not sure where the
script is not right.

Can any one help ?

 

[Jim Cone]

RS,

Change "&" to "and" in the following line and see what happens...
If cell.Value = txtDate & cell.Offset(0, 1).Value = txtName Then

Jim Cone
San Francisco, USA

Ok, I have 4 columns:
Department, Date, Employee Name, Daily HR attendance code
so
A, B, C, D
Headers are on row 1, everything below that is data...
So what I wanted to be able to do, is change the HR code with a script,
incase managers make a mistake.
So, first first the Date from B, then in Offset find the right name to
match the date, and then if those two match change the HR code using
offset on the same row.
starts here -------------

Sub changedhrcode()

Dim lastrow As Long
Dim cell As Range
Dim txtDate As Date
Dim txtName As String
Dim txtHR As String

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")

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

End Sub

So I highlighted a row that I wanted to test this script on, I enter
the Date, then the Name, and then the new HR code that was supposed to
replace it, but it doesn't replace it, so I am not sure where the
script is not right.
Can any one help ?

 

[RompStar]

yep, that's what the problem was, I aldo Dimed txtDate as Date, it
works now, thanks.

 

[RompStar]

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 ?

 

[Jim Cone]

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



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 ?

 

[RompStar]

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.