Macro Changing Columns Question

[distraughtintern]

Hi,

I am writing a fairly simple macro that will add the values in one column(weekly) to the adjacent column (Year To Date) and have the adjacent column maintain (Year To Date) show the sum of the Weekly column plus the previous value of Year To Date Column. I have done this but this now needs to be repeated among 9 other pairs of columns. I have succesfully wrote my first loop to go down cell by cell but how do I get excel to move to adjacent columns? I tried doing a long if statement but that doesnt work. Heres the code. Ideas? I think I need to get rid of the IFs but I can't figure out any other way to do it.

Sub addstuff()
'
Dim lCalculateYTD As Long
Dim lWeekly As Long
Dim sCellNumber As String
Dim iCounter As Integer
Dim iCounterTwo As Integer
Dim lRowCount As String
Dim lColumnCount As String
Dim lWeeklyCol As String
Dim lYtdColumn As String
Dim lMyCount As String


Sheets("Input").Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
lRowCount = Selection.Rows.count
lRowCount = lRowCount - 1

Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
lColumnCount = Selection.Columns.count
lColumnCount = lColumnCount - 1



lWeeklyCol = "A"
lYtdColumn = "B"
Debug.Print lWeeklyCol




For iCounterTwo = 1 To lColumnCount / 2
Debug.Print iCounterTwo

sCellNumber = "3"

If lWeeklyCol = "A" Then
lWeeklyCol = "C"
ElseIf lWeeklyCol = "C" Then
lWeeklyCol = "E"
ElseIf lWeeklyCol = "E" Then
lweeklcol = "G"
ElseIf lWeeklyCol = "G" Then
lWeeklyCol = "I"
ElseIf lWeeklyCol = "I" Then
lweeklcol = "K"
ElseIf lWeeklyCol = "K" Then
lWeeklyCol = "M"
ElseIf lWeeklyCol = "M" Then
lweeklcol = "O"
ElseIf lWeeklyCol = "O" Then
lWeeklyCol = "Q"
ElseIf lWeeklyCol = "Q" Then
lweeklcol = "S"
ElseIf lWeeklyCol = "S" Then
lWeeklyCol = "U"
End If
Debug.Print lWeeklyCol

If lYtdColumn = "B" Then
lYtdColumn = "D"
ElseIf lYtdColumn = "D" Then
lYtdColumn = "F"
ElseIf lYtdColumn = "F" Then
lYtdColumn = "H"
ElseIf lYtdColumn = "H" Then
lYtdColumn = "J"
ElseIf lYtdColumn = "J" Then
lYtdColumn = "L"
ElseIf lYtdColumn = "L" Then
lYtdColumn = "N"
ElseIf lYtdColumn = "N" Then
lYtdColumn = "P"
ElseIf lYtdColumn = "P" Then
lYtdColumn = "R"
ElseIf lYtdColumn = "R" Then
lYtdColumn = "V"
End If


For iCounter = 1 To lRowCount
lWeekly = Range(lWeeklyCol & sCellNumber)
lCalculateYTD = Range(lYtdColumn & sCellNumber)
lCalculateYTD = lCalculateYTD + lWeekly
Range("D" & sCellNumber) = lCalculateYTD

sCellNumber = sCellNumber + 1
Next

Next

 

[AlphaFrog]

Sub addstuff2()

     Dim Lastrow As Long, Lastcol As Long, col As Long
     
     Sheets("Input").Select
     
     Application.ScreenUpdating = False
     
     Lastrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row          'Last used row on the sheet
     Lastcol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column    'Last used column on the sheet
     
     For col = 3 To Lastcol Step 2                                          'Loop through column pairs starting at column C
        Range(Cells(3, col), Cells(Lastrow, col)).Copy                      'Copy 1st of the column pairs (C3 to C last-used-row)
        Range(Cells(3, col), Cells(Lastrow, col)).Offset(, 1).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False 'Add the copied column to the 2nd column of the pair
     Next col
     
     Application.CutCopyMode = True 'Clear the clipboard
     Application.ScreenUpdating = True
     
End Sub

 

[distraughtintern]

Thank you so much. This works extremely well and I was able to use the same logic and apply it to other problems.