Offset value in other columns if column A has a given value

[Pedus]

Hi everyone.

I hope the title is OK.

I am working on a rather primitive accounting sheet, where I have three columns, each containing a fiscal year and their corresponding values below depending on the account they represent. The workbook itself is quite big with many macros inside, but I have made a shortened version below to better understand my problem. Just for the sake of me needing a VBA code, I have shortened it down to these:
Column A: To be input with either blank, “D” for Do Nothing or “M” for Manual
Column B: Account name
Column C: The value for This fiscal year
Column D: The value for This fiscal year-1
Column E: The value for This fiscal year-2

Now, every year I have to reset the values of “this fiscal year”, and move all the data one step to the right. I want to be able to do this by pressing a button within the sheet. I found a suitable way to do this by using the code below, where I have values within the range of C2:E6. The values in C2 and D2 is based on the sum of other cells within the sheet, but the value in E2 has to be put manually, because it is the first shown year.

Sub ResetSheetForNextYear()
ActiveSheet.Range("E2").Value = ActiveSheet.Range("D2").Value
Range("D3:D6").Offset(0, 1).Value = Range("D3:D6").Value
Range("C3:C6").Offset(0, 1).Value = Range("C3:C6").Value
Range("C3:C6").ClearContents
End Sub

This code moves all values one step to the right, starting with the values in column D moving to E. After everything has moved one step to the right, I clear the content in column C, making it ready for the new numbers. As I said, the values in C2 and D2 is given by a sum of other cells within the sheet, so they are automatically correct when all other cells have been moved one cell to the right.

What I desire, and cannot figure out:
Given the range of say A1:A500, if the value of a cell in column A is blank, I want that particular rows numbers from C and D to be offset one step, like in my code.
Also, if the value of a cell in column A is “D”, I do not want the offset code to be run on that particular row.
If the value of a cell in column A is instead “M”, I want to run the code where only the value from column D is moved to E. There is a perfectly good reason for that, but it is not something that needs to be explained to understand the question ?
So basically I need a loop of sorts that checks every row in the range for these three requirements, and do the corresponding action depending on the values in column A.

I greatly appreciate all feedback – I do very little VBA work, but I think it works wonders for many of my projects!

Should you require a better visual understanding, I will provide this later on. It is too late at night for me to be making one now.

Yours sincerely,
Pedus

 

[Pedus]

I found a solution myself. It is not pretty, but it works

Sub Test()
    Dim i As Long
    Dim o As Long
    For o = 1 To 500
        If Range("A" & o).Value = "M" Then
        ActiveSheet.Range("E" & o).Value = ActiveSheet.Range("D" & o).Value
        
        End If
        
    Next o
    For i = 2 To 500
        If Range("A" & i).Value = "" Then
            Range("D" & i).Offset(0, 1).Value = Range("D" & i).Value
            Range("C" & i).Offset(0, 1).Value = Range("C" & i).Value
            Range("C" & i).ClearContents
            
        End If
    Next i
    
End Sub

In this code, I simply did not define "D", so if none of the other two statements were true, nothing had to be done.