HI There.
I have exhausted all avenues trying to figure out an excel problem.
I have a worksheet with a macro attached. The macro performs different actions depending on the data entered in specific columns. for example, is a name is entered in column A, date is automatically entered in column B. When a drop down value is entered in Column L, date is entered in Column L. If data in column L = "Fees Received" or "Policy No. Issued" data is copied to another worksheet. All individual components are working. However not all the time. I am new to VBA but believe I have the incorrect logic in my code. I would be very grateful for any help.
Kind Regards
Eithne
A couple of things to note -
- the drop downs dont work as you have not added the sheets the validation is on so i cant check in great detail
- i also wouldnt use lables to move around code, you can eitiher put what you want to action in the if statement orif you will reuse in its own sub and then call it.
- Becareful about using the onchange method as every change you make anywhere in the worksheet will run through the code
I am not sure there is a great deal wrong with the logic, however the code is very long and most of it isnt doing a lot
For example you have
If Target.Column = 1 Then
GoTo AddEntryDate
End If
and all of this just to add a date
AddEntryDate:
'Update on 11/11/2019 -If data changes in column L Activity , insert
'today's date into column M - Date of Activity
Dim WorkRng As Range
Dim rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each rng In WorkRng
If Not VBA.IsEmpty(rng.Value) Then
rng.Offset(0, xOffsetColumn).Value = Now
rng.Offset(0, xOffsetColumn).NumberFormat = "dd/mm/yyyy"
rng.Offset(3, xOffsetColumn).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
Exit Sub
Where as you could have something like the below for the first 2 dates
If Target.Column = 1 Then
Target.Offset(0, 1) = Format(Now(), "DD/MM/YYYY")
End If
If Target.Column = 12 Then
Target.Offset(0, 1) = Format(Now(), "DD/MM/YYYY")
End If
I cant really test the last 2 due to the missing sheets
