VBA solution NEEDED!!

laceygirl

New Member
Joined
Jun 19, 2014
Messages
13
ill try to show you what I needing.

column A / Column B / column c / column d / column e/ column F/
Current Employees / HIRE DATE/ TERM DATE / NO SHOW/ QUIT SAME DAY/ PREVIOUS EMPLOYEE/



when we hire someone I put their name in column a and then the hire date in b. when or if they quit get fired or don't show up etc, column c,d, and e will then have a date. At that point I want the name of that person to go from column a to column f automatically after I put the date into one of those columns. THANKS
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
in F2 try
=IF(C2&D2&E2 <> "",A2,"")
 
Upvote 0
yes thank you for the function it does well but yes I am trying to get the name from column a to column f. Thanks!
 
Upvote 0
I believe this in the Worksheet object of the sheet with the data will do what you are looking for.
Note, if you make a mistake, and remove the date, the name will NOT move back to column A, however,
if you enter a date in column C and then a date in column D, the value in column F will not be overwritten with a blank.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("C:E")) Is Nothing Then  'the changed cell is in the date range to watch
        
        If IsDate(Target.Value) Then    'a date was just added in the watch range
            
            If Range("A" & Target.Row).Value <> "" Then 'only copy if there is a name in column A of the row changed
                
                Range("F" & Target.Row).Value = Range("A" & Target.Row).Value
                Range("A" & Target.Row).Value = ""
        
            End If
        End If
    End If
End Sub
 
Upvote 0
I believe this in the Worksheet object of the sheet with the data will do what you are looking for.
Note, if you make a mistake, and remove the date, the name will NOT move back to column A, however,
if you enter a date in column C and then a date in column D, the value in column F will not be overwritten with a blank.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("C:E")) Is Nothing Then  'the changed cell is in the date range to watch
        
        If IsDate(Target.Value) Then    'a date was just added in the watch range
            
            If Range("A" & Target.Row).Value <> "" Then 'only copy if there is a name in column A of the row changed
                
                Range("F" & Target.Row).Value = Range("A" & Target.Row).Value
                Range("A" & Target.Row).Value = ""
        
            End If
        End If
    End If
End Sub
You should really disable events when you have the Change event change values in cells so the event doesn't get raised again with each change you make in code. Here is how I would write it (note I do not check if the value entered in the Target cell was a date or not as those are date columns).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("C:E")) Is Nothing Then
    If Len(Cells(Target.Row, "A")) > 0 And Len(Target.Value) > 0 Then
      Application.EnableEvents = False
      Cells(Target.Row, "A").Cut Cells(Target.Row, "F")
      Application.EnableEvents = True
    End If
  End If
End Sub
 
Upvote 0
Please help I have tried to add this many times to my workbook but it isn't working with the steps I have found online. Is there anyway I can get a walk through on how to add and play this to my workbook.
 
Upvote 0
Please help I have tried to add this many times to my workbook but it isn't working with the steps I have found online. Is there anyway I can get a walk through on how to add and play this to my workbook.

I am reposting Rick's code because it is the most succinct and appropriately disables event handling

Open the Visual Basic Window (ALT+F11)
In the Project window on the left of the screen you should see a listing of all of the open workbooks and sheets in those workbooks.
Find the Worksheet that you want to have this run in (the one where you will be entering the dates) and double-click it.
This should change the main part of the screen to show the code that is entered for that object.

In that main area, paste the following code...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("C:E")) Is Nothing Then
    If Len(Cells(Target.Row, "A")) > 0 And Len(Target.Value) > 0 Then
      Application.EnableEvents = False
      Cells(Target.Row, "A").Cut Cells(Target.Row, "F")
      Application.EnableEvents = True
    End If
  End If
End Sub

Then when you make changes, it should do what you need.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top