Results 1 to 2 of 2

Thread: Offset value in other columns if column A has a given value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Code:
    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

  2. #2
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Offset value in other columns if column A has a given value

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

    Code:
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •