Overwrite the value if other cell is not blank

argandoz

New Member
Joined
Mar 5, 2009
Messages
13
Hi! Guys,

Anyone can help me find a formula for :

Column A (Plan Date) and Column B (Date Recieved) contain date value. If B2 is blank, A2 will maintain the date entered in it. However if B2 has date (either earlier or later than A2) it will supersede the date with the date value in B2.


Thanks.

Argandoz
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Right on the sheet tab, select view code and paste this:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("B2").Value <> Range("A2").Value Then
Range("A2").Value = Range("B2").Value
End If

End Sub
 
Upvote 0
Hi C_M,

Thanks but if I enter a date in A2 and b2 is blank, A2 is blank. A2 must maintain the date it has earlier.

Furthermore, it should apply to column a and column b.


Thanks,

argandoz
 
Upvote 0
try this modification of what's already posted:
Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

if not len(range("b2") = 0) or not range("b2") = "" then
   If Range("B2").Value <> Range("A2").Value Then
     Range("A2").Value = Range("B2").Value
   End If
end if

End Sub
 
Upvote 0
Hi C_M,

Thanks but if I enter a date in A2 and b2 is blank, A2 is blank. A2 must maintain the date it has earlier.

Furthermore, it should apply to column a and column b.


Thanks,

argandoz

Try:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If IsEmpty(Range("B2")) Then
Range("A2").Value = Range("A2").Value
Else
Range("A2").Value = Range("B2").Value
End If

End Sub

Dont understand what you mean by "should apply to both A and B".
 
Upvote 0
Thanks C_M,

It's working now the way it should.

Furthermore, it should work also to a3 to b3, a4 to b4 and so on.

if a3 has date and b3 has date, a3 will take the value in b3, likewise for a4 to b4, a5 to b5 and so on the descending rows.


Thanks,

Argandoz
 
Upvote 0
Argandoz,

Try:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
If IsEmpty(Cells(i, "B").Value) Then
Cells(i, "A").Value = Cells(i, "A").Value
Else
Cells(i, "A").Value = Cells(i, "B").Value
End If
Next i
End Sub

When you say "the descending rows", I am assuming that the rows are variable. Are you expecting anything other than dates in column B? If yes, then I think you would need Isdate condition check in that loop. If the rows are fixed, then replace lastrow in the code with the last row number that contains date.
 
Upvote 0
Dear C_M,

That really solve it. Thank you very much for extending your helping hand.


Regards,

Argandoz
 
Upvote 0
Dear C_M,

I think you are correct. Heading at B1 is also the value now in A1. And then at Row 50 is again headings of sub-data where value in B50 should be different from A50. Right now, A50 and B50 have the same value.


Kindly check and advise proper code to resolve the hicup.


Thanks,


Argandoz
 
Upvote 0
Argandoz,

Try:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lastrow
If IsEmpty(Cells(i, "B").Value) Then
Cells(i, "A").Value = Cells(i, "A").Value
Else
If IsDate(Cells(i, "A").Value) And IsDate(Cells(i, "B").Value) Then
Cells(i, "A").Value = Cells(i, "B").Value
End If
End If
Next i
End Sub

Does it work out?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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