Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

move value from cell to another based on condition

0

hi

I  try  making  code  when  show  the (DATE+10) in column B  then  should  move  amount  from column C to  column D   if  it's  not  then  shouldn't  happen any thing

I put   the  expected result  in sheet2

this  is  my  code  but it doesn't work at all ! 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(2)) Is Nothing Then
If Target.CountLarge > 0 Then Exit Sub
Dim lr As Long
lr = sheet1.Cells(Rows.Count, 2).End(3).Row
With Target
If .Value = Date + 10 Then
Range("d" & lr) = Range("c" & lr)
Range("c" & lr).ClearContents
End If
End With
End If

End Sub

note  :  the  date   is  not  today  . it  based  on  column A   . it  should  equal date  in column A  and add 10 

excel
MS Office Version: 2019
posted by: Hasson (rep: 30) on Tue Nov 2, 2021 at 4:09 am
Answer
Discuss

Answers

0
Selected Answer

Hasson

Firstly, in VBA, Date will return today's date (so shouldn't be in your code).

This code (in the attached file) just uses the Offset method to test and move the value in C to D  if a date is typed into a cell in column B which is 10 days after that in column A (but note that for A3 21/10/2021, 10 days on is 31/11/2021 not 01/11/2021 as in your  expected results).

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(2)) Is Nothing Or Target.CountLarge > 1 Then Exit Sub

Application.EnableEvents = False 'avoid re-triggering this if values are moved

With Target
    If .Value = DateValue(.Offset(0, -1).Value) + 10 Then
        .Offset(0, 2) = .Offset(0, 1).Value
        .Offset(0, 1).ClearContents
    End If
End With

Application.EnableEvents = True
End Sub
Suggest you look carefully at your test (in bold above) and modify the 10 if needed. Also, if you want instead to run a macro once to check all values in B, then don't use the Worksheet_Change event.

Hope this helps.

posted by: John_Ru (rep: 6142) on Tue Nov 2, 2021 at 7:13 am
Discuss

Discussion

Hi John,
if you want instead to run a macro once to check all values in B
you're  right.  that's missed me. 
thanks  very much
Hasson (rep: 30) Nov 2, '21 at 7:27 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login