Copy Paste and Delete a cell based on condition

ipon70

Board Regular
Joined
May 8, 2013
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
Here is my dilemma.
E4 contains nothing until it says "100"
H4 contains a date found on another sheet, that updates daily.
F4 needs to contain the date from H4 when E4 says 100, but once F4 contains that date, never check or copy or update that date again.

That will then need to repeat from E4 down to E18 with each row corresponding to its own row...ie(E4,H4,F4 and E5,H5,F5) and so on.

Any help would be awesome as I have spent entirely to much time on this project already.

Thanks in advance.
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, Range([E4], Cells(Rows.Count, "E").End(xlUp)))
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cel In rng
    If cel = 100 And cel(1, 2) = "" Then cel(1, 2) = cel(1, 4).Value
Next
Application.EnableEvents = True
End Sub
 
Upvote 0
Let me give this a try if this works you will be a life saver.

Thanks
 
Upvote 0
Did you put the macro in the sheet module (copy the macro, right-click the sheet tab, select View Code, paste to the window on the right).
 
Upvote 0
Yes that is actually exactly how I did it, but for the sake of argument I went back in and deleted it, exited the doc, then opened it again and pasted it. Same results, which is nothing.
 
Upvote 0
It works for me.
It runs whenever there is a change to cell(s) in column E.
If the column E cell contains 100 and​ the column F cell is blank, the column H date is put in column F.

Try putting a breakpoint on the first line of the macro, trigger the macro, and step through via F8 to see what's happening.

How are values in column E changed?
 
Last edited:
Upvote 0
Column E is a formula that does this "=IF(D4>=100%,100,"")"
Column H is a formula that does this "=INDEX('OJT TIME'!$B$6:$AP$6,,MATCH("MS",'OJT TIME'!B7:AP7,0)*1)"

Column H updates and changes pretty much everyday but I need Column F to get the very first date that ever shows up in Column H and hold it. This is basically an On the job training sheet and we need to know when the first date is that they get their minimum amount of hours, training continues still each day on each task, but the date when the hit the minimum needs to be frozen and held.
 
Upvote 0
Try this :
Code:
Private Sub Worksheet_Calculate()
Dim rng As Range, cel As Range
On Error Resume Next
Set rng = Range([E4], Cells(Rows.Count, "E").End(xlUp).Offset(0, 1)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cel In rng
    If cel(1, 0) = 100 Then cel = cel(1, 3).Value
Next
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Well something is at least happening now. The F4 actually did fill in with "#N/A, but my entire H column change to "#N/A". So not sure why the #N/A happened but it did actually pull something.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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