jason_kelly
12-08-2010, 08:46 AM
Hi There,
I need your help,
For some reason, I cant get the code below to work to move the data over to another worksheet "Inactive_Data" and delete it from the existing worksheet when a textbox in form contains the value: "Inactive".
Any ideas?
Ps. I have also attached the spreadsheet for ease of reference.
'=============================================================
'MODIFY FUNCTION [+/-]
'=============================================================
Private Sub V3_MOD()
Dim rFind As Range, ws As Worksheet
For Each ws In Sheets(Array("Active_Data", "Inactive_Data"))
Set rFind = ws.Columns.Find(What:=Me.h3, LookAt:=xlWhole)
If Not rFind Is Nothing Then
MsgBox rFind.Offset(, 8)
rFind.Offset(, -2) = Me.h1
rFind.Offset(, -1) = Me.h2
rFind.Offset(, 0) = Me.h3
rFind.Offset(, 1) = Me.h4
rFind.Offset(, 2) = Me.h5
rFind.Offset(, 3) = Me.h6
rFind.Offset(, 6) = Me.h7
rFind.Offset(, 7) = Me.h8
rFind.Offset(, 9) = Me.h10
rFind.Offset(, 10) = Me.h11
rFind.Offset(, 11) = Me.h12
rFind.Offset(, 12) = Me.h13
If notify.Value = True Then
rFind.Offset(, 4) = "Yes"
Else
rFind.Offset(, 4) = "No"
End If
If remind.Value = True Then
rFind.Offset(, 5) = "Yes"
Else
rFind.Offset(, 5) = "No"
End If
rFind.Offset(, 8) = Me.h9
If rFind.Offset(, 8) = "Inactive" Then
ws.Range("A" & CurRow).Cut Destination:=Sheets("Inactive_Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
ws.Range("A" & CurRow).EntireRow.Delete xlUp
End If
Exit Sub
End If
Next ws
End Sub
Much thanks and appreciation for all your help.
Cheers,
Jay
I need your help,
For some reason, I cant get the code below to work to move the data over to another worksheet "Inactive_Data" and delete it from the existing worksheet when a textbox in form contains the value: "Inactive".
Any ideas?
Ps. I have also attached the spreadsheet for ease of reference.
'=============================================================
'MODIFY FUNCTION [+/-]
'=============================================================
Private Sub V3_MOD()
Dim rFind As Range, ws As Worksheet
For Each ws In Sheets(Array("Active_Data", "Inactive_Data"))
Set rFind = ws.Columns.Find(What:=Me.h3, LookAt:=xlWhole)
If Not rFind Is Nothing Then
MsgBox rFind.Offset(, 8)
rFind.Offset(, -2) = Me.h1
rFind.Offset(, -1) = Me.h2
rFind.Offset(, 0) = Me.h3
rFind.Offset(, 1) = Me.h4
rFind.Offset(, 2) = Me.h5
rFind.Offset(, 3) = Me.h6
rFind.Offset(, 6) = Me.h7
rFind.Offset(, 7) = Me.h8
rFind.Offset(, 9) = Me.h10
rFind.Offset(, 10) = Me.h11
rFind.Offset(, 11) = Me.h12
rFind.Offset(, 12) = Me.h13
If notify.Value = True Then
rFind.Offset(, 4) = "Yes"
Else
rFind.Offset(, 4) = "No"
End If
If remind.Value = True Then
rFind.Offset(, 5) = "Yes"
Else
rFind.Offset(, 5) = "No"
End If
rFind.Offset(, 8) = Me.h9
If rFind.Offset(, 8) = "Inactive" Then
ws.Range("A" & CurRow).Cut Destination:=Sheets("Inactive_Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
ws.Range("A" & CurRow).EntireRow.Delete xlUp
End If
Exit Sub
End If
Next ws
End Sub
Much thanks and appreciation for all your help.
Cheers,
Jay