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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,788
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
in F2 try
=IF(C2&D2&E2 <> "",A2,"")
 

laceygirl

New Member
Joined
Jun 19, 2014
Messages
13
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!
 

laceygirl

New Member
Joined
Jun 19, 2014
Messages
13

ADVERTISEMENT

well I mean moved to column f and then deleted from column a.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

laceygirl

New Member
Joined
Jun 19, 2014
Messages
13
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.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,665
Members
418,147
Latest member
dorkas

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
Top