Solved

Automate change module

Posted on 2014-07-23
12
101 Views
Last Modified: 2014-07-24
Folks,
The code represents only "Jan 14". I have 11 other months that also represent the year 14.
Sub UpdateJanuaryFormulas()
'
' UpdateJanuaryFormulas Macro
'

'
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    
    Range("K6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("L6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("M6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("N6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"

    Range("O6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("P6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("Q6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("R6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"

    Range("S6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("T6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("U6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
  

    
    
    
    
    
End Sub

Open in new window

I also have another module that appends tabs labeled Jan - Dec such that it would now be Jan 14....Dec 14.
The user is requested to put in the 2 digit year, i.e. 14. After the tab names have been updated I would like to be able to change the in the module the above module to replaced the 14 with the new 2 year digit. If they put in a 15 then the above module would not read ='Jan 14' but "Jan 15. As I mentioned, I have 11 other modules with the same problem. The code bellows show the user ask to enter in a 2 digit year. This occurs at line 26:
Sub Append()

    Dim strFileSelected As String
    Dim objOfficeDialog As Object
    Dim wbDestination As Workbook
    Dim wbSource As Workbook
    Dim sh As Worksheet
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Set objOfficeDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set wbDestination = ActiveWorkbook
    
    With objOfficeDialog
        .Title = "Select the Project Cost Allocation file"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        strFileSelected = .SelectedItems(1)
    End With
Dim strSuffix As String
If strFileSelected <> "" Then
        strSuffix = InputBox("Please enter text to append to tab names")
        Set wbSource = Workbooks.Open(strFileSelected)
        
        For Each sh In wbSource.Sheets
            sh.Copy After:=wbDestination.Worksheets(wbDestination.Worksheets.Count)
            wbDestination.Worksheets(wbDestination.Worksheets.Count).Name = sh.Name & " " & strSuffix
           ' wbDestination.Worksheets(wbDestination.Worksheets.Count).Cells.Copy
           ' wbDestination.Worksheets(wbDestination.Worksheets.Count).Cells.PasteSpecial xlPasteValues
        Next sh
        
        wbSource.Close False
    End If
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
    

    ReturnMenu
    UpdateFormulas
    MsgBox "Completed"
    Range("A1").Select
   

Open in new window

The changes to the monthly modules (which I could consolidate into one) need to be made before I call the module "ReturnMenu"
I hope I've been clear.
0
Comment
Question by:Frank Freese
  • 8
  • 4
12 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Unless I've totally misunderstood what you asked for in the previous thread you no longer need the individual month macros.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Lines 13 ad 14 get 2 digit the year from the user inputed 4 digit year.
Sub AddYear()

  ' add year
    Dim Yearly As String
    Dim lngIndex As Long
    Dim strYr As String
    
    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
    'Yearly = Trim(Yearly)
    Range("B6:B17").Select
    Selection.NumberFormat = "General"
    Selection.Value = Yearly
    strYr = " " & Right$(Yearly, 2)
    'Append worksheet
    Append
    ReturnMenu
'    UpdateFormulas

    For lngIndex = 1 To 12
        ' MonthName(lngIndex, True) returns "Jan" when lngIndex is 1
        Range("C" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("D" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("E" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("F" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("G" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("H" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("I" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("J" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        
        Range("K" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("L" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("M" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("N" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    
        Range("O" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("P" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("Q" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("R" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    
        Range("S" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("T" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    Next

    MsgBox "Completed"
    Range("A1").Select
    End Sub

Open in new window

0
 

Author Comment

by:Frank Freese
Comment Utility
Exactly. This is a problem I just realized that I forgot to address.
I have 12 modules, one for each month, that I could easily combine into one module called ChangeFormulas.
The code above ONLY applies if the year is 14 (Jan 14). If the year is 15, I do change the tabs BUT not the year in ...hey that's not fair. It looks like you posted a solution before I could answer your question here :). Let me test your solution.
0
 

Author Comment

by:Frank Freese
Comment Utility
From what I see you just eliminated 13 modules? The above seems to be working just fine.
0
 

Author Comment

by:Frank Freese
Comment Utility
It works fine for Jan.
I just need to expand for the other 11 months - true. February looks like this:
Sub UpdateFebruaryFormulas()
'
' UpdateFebuaryFormulas Macro
'

'
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("I7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("J7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("K7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("L7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("M7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("O7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("P7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("Q7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("R7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("S7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("T7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("U7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
End Sub

Open in new window

It looks like all I need to do is change:
"'!R[31]C"

Open in new window

for Jan to this:
"'!R[30]C"

Open in new window

for February.
True?
0
 

Author Comment

by:Frank Freese
Comment Utility
Works fine for Jan - trying to figure of why Feb and the rest of the months aren't working now.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
From what I see you just eliminated 13 modules? The above seems to be working just fine.
That's good and it's not really new code. It is what I first posted in our last thread where you asked how it fit in with the summary.

Works fine for Jan - trying to figure of why Feb and the rest of the months aren't working now.
What works fine? What do you mean when you say "Feb and the rest of the months aren't working now.".
0
 

Author Comment

by:Frank Freese
Comment Utility
Here's what I did. I first copied the code provided in  ID: 40215764 and replaced what was in AddYear with that code. I did have to make 1 change since  
Range("U" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"

Open in new window

was overlooked. No problem.
Ran the program with the changes and the formulas for January were correct. For example, cell C6  ='Jan 17'!B37 and that was what I was expecting..
Only the January formulas were correct. So I figured I need to add February and that's where I loose it. Taking the new code provided for January from AddYear, I copied that to Word. The only difference I could see between your January (as well as my old January code) and the code I was using for February was where the row in January was !R[31] and in February it was !R[30].
So I pasted your January to the end of the code provided just before the "Next" statement gave the new section a 'February remark so I'd know where February began. I then selected the all the code I'd just pasted and changed the code to !R[30] (this is February) from !R[31] (this is January). I copied all that code from Word and replaced what was in the module AddYear that had only January in it at the time. I made no changes to January, it was working. I cleared out my formulas and deleted worksheets I had added to test January. I then tried the program again. What I got was the formula in Jan cell C6 was now 'Jan 17'!B36 from 'Jan 17'!B37 (which was correct) so I was now off by one in January after I added February.
I must be doing something incorrect in adding months after January, but what?
I got my code from recording a macro for each month.
Hopefully, I've been clearer.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
Yes you were clearer and the problem is my fault because one, I really didn't take a close look at the results, and two, I don't fully understand the FormulaR1C1 method when I tried to generalize the formula creation.

In the code there is a loop that goes from 1 to 12 which generates formulas for the 12 months. It obviously went wrong when it got to 2 (February) but there's no need to create separate code. Here's a change that uses 'Formula' rather than FormulaR1C1. Give it a try and if there's still some problems with it please let me know. Note line 43 which wasn't there before.

Sub AddYear()

  ' add year
    Dim Yearly As String
    Dim lngIndex As Long
    Dim strYr As String
    
    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
    'Yearly = Trim(Yearly)
    Range("B6:B17").Select
    Selection.NumberFormat = "General"
    Selection.Value = Yearly
    strYr = " " & Right$(Yearly, 2)
    'Append worksheet
    Append
    ReturnMenu
'    UpdateFormulas

    For lngIndex = 1 To 12
        ' MonthName(lngIndex, True) returns "Jan" when lngIndex is 1 '='Jan 14'!B37
        Range("C" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!B37"
        Range("D" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!C37"
        Range("E" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!D37"
        Range("F" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!E37"
        Range("G" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!F37"
        Range("H" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!H37"
        Range("I" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!I37"
        Range("J" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!J37"
        
        Range("K" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!K37"
        Range("L" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!L37"
        Range("M" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!M37"
        Range("N" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!N37"
    
        Range("O" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!O37"
        Range("P" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!P37"
        Range("Q" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!Q37"
        Range("R" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!R37"
    
        Range("S" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!S37"
        Range("T" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!T37"
        ' I added this line. Previously there was no formula for "Non-Billable"
        Range("U" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!U37"
    Next

    MsgBox "Completed"
    Range("A1").Select
    End Sub

Open in new window

0
 

Author Comment

by:Frank Freese
Comment Utility
I noticed that this morning. Let me give this a shot. I have a "option" but everyone would probably laugh at my code. Will get back shortly
0
 

Author Comment

by:Frank Freese
Comment Utility
I'm sending it to the client for testing, but it looks great from here. Will let you know.
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
great change
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now