I have no adequate test data and no access to Excel 2013 so I cannot duplicate your syntax error nor check that my macro will do what you want.
From your use of Macro Recorder generated code, I suspect you are very new to VBA. There are several sections to this answer. In each section I attempt to explain something you do not know or appear to misunderstand. There is a lot of information here. I suggest you read it slowly and try to understand each paragraph. If you cannot understand a paragraph., move on and come back later. I believe you will have to understand everything here eventually if you want to become a VBA programmer. But you do not have to understand it all today.
I suspect the problem is the long, complicated formula for column V. I recall that for Excel 2003, the maximum length for a formula was 255 characters. There was also a limit on the nesting. Later versions allow for much longer formulae and more nesting. There is a suggestion that some versions of the VBA compiler will reject a formula that Excel would not. In my macro, I have broken your long formula into parts which are both shorter and easier to understand.
What is giving the error: Editor or Compiler or Interpreter
You complain: “It also seems to skip everything before, meaning not adding the 2 new columns and not calculating column U "Dates"”. From this, I deduce you do not understand the various stages and what is checked when.
The Editor checks as you type the source code. Write If A=1 and the Editor will immediately tell you that If is not valid without Then.
If you click [Debug] then [CompileVBAProject], you are compiling every macro in every module. If you click [Run] or [F5], you are running the Compiler for the current macro which will start the Interpreter if the Compiler does not discover an error.
The compiler coverts the source code to immediate code unless it discovers an error. That is, the Compiler converts something that is easy for a human to read to something that is easy for the Interpreter to read. The Compiler can spot errors such as “variable not defined” or “variable defined twice”.
The Interpreter is the program which runs your VBA macro. If you write something like Cells(RowCrnt, ColCrnt).Value = 1, it is the Interpreter that notices that the current value of RowCrnt is 0 and reports that there is no such row.
If the Interpreter had found an error when trying to create column V, the early parts of the macro would have been obeyed and you would have had the new column headers and the formula in column U.
If the Editor had found an error, the message appears as you finish a line. If you accept the message and leave the line, it goes red.
If you clicked [Run] or [F5] and nothing seems to have happened before the error was reported, it will because the Compiler that has discovered the error. A little confusingly with [Run] or [F5], only the first macro has been compiled before the Interpreter is started. If the first macro calls a second macro that second macro is only compiled when it is called. If that second macro contains an error it will be reported. The statements in the first macro up to the call of the second macro will have been performed but no statements in the second macro will have been performed.
Sometimes an error message is totally clear and you know how to correct it. On other occasions, identifying what has reported the error can be very helpful.
Macro Recorder
The Macro Recorder does not know what you are trying to do. It records what you are doing, statement by statement, without any understanding of what you are trying to achieve. For example:
You moved the cursor to cell U6
You typed “Dates”
You moved the cursor to cell V6
You typed “Status”
The Macro Recorder has recorded this as:
Range("U6").Select
ActiveCell.FormulaR1C1 = "Dates"
Range("V6").Select
ActiveCell.FormulaR1C1 = "Status"
This is syntactically correct VBA but it is poor VBA. Selecting a cell is a slow command because the interpreter has to repaint the screen so the selected cell is visible. You can speed up selecting a cell by adding Application.ScreenUpdating = False at the top of your macro but it will still be slow.
A programmer would write:
Range("U6").Value = "Dates"
Range("V6").Value = "Status"
because the programmer knows they do not need to move the cursor.
Actually, a good programmer would not write that. This code only works on a particular worksheet. Suppose that worksheet is named “Tasks” a good programmer would write something like:
With Worksheets("Tasks")
.Range("U6").Value = "Dates"
.Range("V6").Value = "Status"
End With
The Macro Recorder will record Worksheets("Data").Activate if you switch worksheets but does not record which worksheet was active when it started recording.
Adding the With and End With statements and adding a period in front of Range, has two benefits. (1) If the wrong worksheet is active when the macro is started, it will still work. Without the With your macro would have written all over some other worksheet and the UnDo command will not undo what a macro has done. (2) When you, or someone else, needs to update this macro in six or twelve months, it will be clear which worksheet is the target of this code.
Next you have:
Range("T6").Select
Application.CutCopyMode = False
Selection.Copy
Range("U6:V6").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
It took me some time to work out what this code achieved. You are copying the formats from the previous cell of the header to the new cells. Again slow commands although as a one-off that may not matter. More importantly, when someone has to update this macro in twelve months, how much time will they spend deciphering this code?
There are several ways of achieving the same effect. I think I would go for:
With Worksheets("Tasks")
.Range("T6").Copy Destination:=.Range("U6:V6") ‘ Copy everything but only need formats
.Range("U6").Value = "Dates"
.Range("V6").Value = "Status"
End With
In the new statement, Destination:= is optional but I always include it because I think it makes clear what this statement is doing. The comment will help a future maintenance programmer understand the purpose of this statement. Without the comment, it might look as though I was copying the value from cell T6 since not everyone is aware that Copy copies the formatting as well.
The next command is:
Range("S65536").End(xlUp).Select
This statement bothers me a lot. 65,536 was the last row for Excel 2003. Since then the last row is 1,048,576. This is not what the Macro Recorder would record. Did you type this statement? If so, why “S65536”? Are you trying to maintain an Excel 2003 workbook with Excel 2016?
You say you are using both Excel 2016 and Excel 2013. Are you also using Excel 2003? If you are using multiple versions of Excel, you will need to be very careful. Microsoft works hard to make their products backward and forward compatible but that does mean there are 100% compatible. Newer versions have features that do not work in older versions. Excel 2007 was a complete rewrite of Excel 2003 and there were reports at the time of incompatibilities. These incompatibilities were apparently fixed in Excel 2010 which meant Excel 2010 was more compatible with Excel 2003 than with Excel 2007. You cannot assume that a macro written for Excel 2016 will work for earlier versions; it probably will but you must fully test such macros. It is possible the syntax error is caused by an incompatibility between versions although this is not the most likely cause.
The full block when you identify the last row is:
.Range("S65536").End(xlUp).Select
ActiveCell.Offset(0, 2).Select
Set LastRow = ActiveCell
LastRow is not the last row but the last cell in column U. I have seen some disasters caused by misleading names. A maintenance programmer assumes the name of a variable correctly identifies what it contains and writes new code on this assumption.
I have never written a macro for one Excel version to maintain a workbook in another version so you need to test the following if that is what you are doing.
To get the maximum row in column S of the active worksheet, write:
LastRow = Cells(Rows.Count, "S").End(xlUp).Row
To get the maximum row in column S of the worksheet named in the active With statement, write:
LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
Rows.Count returns the number of rows in a worksheet. I have never written a macro for which it mattered which workbook because all worksheets of all open workbooks had the same number of rows. If you are accessing both Excel 2003 and later workbooks, there are different sized worksheets and you need to carefully check that you are getting the count you want.
Note that instead of Range("Xn"), I have written Cells(RowNumber, ColumnId). This is just another way of identifying a cell. RowNumber must be an integer or a integer expression. ColumnId can be a letter (for example "S") or it can be replaced by ColumnNumber, an integer (for example 20) or an integer expression.
If you are accessing ever cell in a column:
For ColCrnt = 1 to 20
" " "
Next
you really need Cells(RowNumber, ColumnNumber) I like to be consistent so I do not have Range in one statement and Cells in the next. I always use Cells whenever I reference a single cell and Range(Cells(TopRow, LeftColumn) , Cells(BottomRow, RightColumn)) for a rectangle. This is not essential but I find consistency pays over time.
Application.ScreenUpdating = False should be at the top of the macro.
I replaced:
'Calculate the value for column U "Date"
Range("U7", LastRow).Select
Selection.FormulaR1C1 = _
"=IF(RC[-2]=""Awaiting Management Response"",R2C1-RC[-9],IF(RC[-3]<>"""",MAX(RC[-3]-RC[-4],R2C1-RC[-3]),R2C1-RC[-4]))"
by
'Set all used cells in column U ("Date") to the required formula
.Range(.Cells(7, "U"), .Cells(LastRow, "U")).Value = _
"=IF(RC[-2]=""Awaiting Management Response"",R2C1-RC[-9],IF(RC[-3]<>""""" & _
",MAX(RC[-3]-RC[-4],R2C1-RC[-3]),R2C1-RC[-4]))"
I have used the Range(Cells(TopRow, LeftColumn) , Cells(BottomRow, RightColumn)) syntax I mentioned above. I have split the long literal because I do not like statements that disappear off the right side of the screen.
I did something similar for column V. While working on column V, I identified a possible problem. The formula for cell V7 is 276 characters long. A formula of this length would have been invalid for Excel 2003 although all documentation I can find suggests it would be valid for all later versions. If you are working with Excel 2003 this might be a problem although I do not understand why it would be spotted at compile time. Having said that, there are reports of a limit on the length of a line which would be a compile time error. You could try abbreviating the strings. So, for example, “MSigDel” instead of “MGMT-SIGNIFICANTLY DELAYED”
An alternative approach would be to split the formula. This is not an easy formula to understand if it was every necessary to amend it. Splitting it would make it much easier to understand and amend. In my final code below, I have split the formula. I have no test data so I cannot promise to have split it correctly but I think I have.
Constants
Having worked through your code line-by-line, I could have stopped amending your macro here. Fixing the poor VBA created by the Macro Recorder had improved the macro but it was still not the macro I would have written if I had your requirement.
I would never write 6, A2, L, Q, R, U or V into my code as you have. At the moment, your column headings are on row 6 but this could change. If an extra column was added columns U and V would have to move. Looking through your code for all the 6s, Us and Vs is a pain and it is very easy to miss one. It is even worse, if two columns are swapped so all the Es become Fs and vice versa. It is so much better to use constants:
Const ColLastExist As String = "T" ' The last column in the main report
Const ColFrstNew As String = "U" ' The first status column
Const ColLastNew As String = "V" ' The last status column
Const RowHead As Long = 6 ' Row containing column headers
You want to move the formatting from the last existing column. This is currently column T but the addition of a new column may change that. Currently, the first new column is U and the last is V. They will move if another column is added the main report. There is also a possibility that an extra new column.
The statement moving the formats changes from:
.Range("T6").Copy Destination:=.Range("U6:V6") ‘ Copy everything but only need formats
to:
' Copy everything from last existing column head but only need formats
.Cells(RowHead, ColLastExist).Copy Destination:= _
.Range(Cells(RowHead, ColFrstNew), Cells(RowHead, ColLastNew))
There is definitely a lot more typing with the second version. But what does the first version mean? Suppose you are looking at this in twelve months because some change is required; perhaps the ranges for delayed, significantly delayed and critical have changed. Would you remember what T6, U6 and V6 are? In the second version the row and the columns are named. The syntax may look a little strange to you but remember that I always use the same syntax for cells and rectangles. If you do the same, you will soon become very familiar with the syntax. More importantly, if the header row, the last column of the main report and or the new status columns have changed, all you need to do is update the constants.
Now consider:
Const ColDates As String = "U" ' The Dates column for the status columns
Const ColSts As String = "V" ' The column for the status string
' Set headers for new status columns
.Cells(RowHead, ColDates).Value = "Dates"
.Cells(RowHead, ColSts).Value = "Status"
which replaced:
.Range("U6").Value = "Dates"
.Range("V6").Value = "Status"
Note that I have defined "U" as both ColDates and ColFrstNew. This gives me one name when I want to refer to the new columns as a range and another name when I want to refer to them individually. I am trying to make things easy for myself and for the maintenance programmer in twelve months. I want to make the meaning of each constant as obvious as possible.
This is a lot to take in at one go. The value of constants will not be obvious until you return to look at this macro in several months’ time. Or perhaps you will write another macro without constants; you will appreciate their value if you ever have to update that macro.
You also use columns L, Q, R and S. You use cell A2. I do not know the purpose of these columns so have made no attempt to name them.
A1 versus R1C1 format
You have used R1C1 format for the formula for the Dates column. I do not like this format. How do you easily work out what cell is referenced by RC[-9]. I have used A1 format for the new formula I have written for the status column.
New macro
Below is my version of your macro plus two of my standard macros. It is a complete replacement for your module.
I have been writing VBA macros for 16 years. The same problem can reoccur many times so I write a macro to solve that problem and keep them in PERSONAL.XLSB. These two standard macros convert a column number to a column code or vice versa.
I have named your worksheet “Tasks”...See line 29. Change my name to yours. Otherwise, this code should work without modification. I have no proper test data but I think I have replaced your long formula with shorter formulae correctly.
Come back with questions as necessary but the more you study my code and try to work out why I have written it as I have, the faster your VBA programming skills will develop.
Option Explicit
Sub ReportingStatus()
' Add new columns to worksheet "Tasks" starting at ColFrstNew.
' The first new column is a number. A postive value indicates the task reported on the row is late
' The second new column is a string indicating the status of the task from Current to Critical.
' The remaining new columns are used to build the string in the second column
Const ClrWhite35 As Long = 10921638 ' Theme colour white, darker 35%
Const ColLastExist As String = "T" ' The last column in the main report
Const ColDates As String = "U" ' The Dates column for the status columns
Const ColFrstNew As String = "U" ' The first status column
Const ColLastNew As String = "V" ' The last status column
Const ColSts As String = "V" ' The column for the status string
Const ColFrstNewBld As String = "W" ' The first column used to build the status string
Const ColLastNewBld As String = "AA" ' The last column used to build the status string
Const RowHead As Long = 6 ' Row containing column headers
Const RowDataFirst As Long = 7 ' First data row
Dim ColCrnt As Long ' For-loop variable for accessing build columns
Dim Formulae As Variant ' Set to an array of formulae
Dim FormulaForSts As String ' Used to build formula for status column
Dim InxF As Long ' Index into array Formulae
Dim RowLast As Long ' Last used row
Application.ScreenUpdating = False
With Worksheets("Tasks")
' Copy everything from last existing column head but only need formats
' Objective is to ensure new column headers look like the earlier ones
.Cells(RowHead, ColLastExist).Copy Destination:= _
.Range(Cells(RowHead, ColFrstNew), Cells(RowHead, ColLastNew))
' Set headers for new status columns.
.Cells(RowHead, ColDates).Value = "Dates"
.Cells(RowHead, ColSts).Value = "Status"
' Find last row of column S. Formulae will be placed on
' rows RowDataFirst to RowLast
RowLast = Cells(.Rows.Count, "S").End(xlUp).Row
'For all used data rows, set cell in column "Dates" to the required formula
.Range(.Cells(7, ColDates), .Cells(LastRow, ColDates)).Value = _
"=IF(RC[-2]=""Awaiting Management Response"",R2C1-RC[-9],IF(RC[-3]<>""""" & _
",MAX(RC[-3]-RC[-4],R2C1-RC[-3]),R2C1-RC[-4]))"
' Load formulae to an array so they can be transferred to cells using a simple loop
Formulae = VBA.Array("=IF(S7=""Awaiting Management Response"",""MGMT-"","""")", _
"=IF(U7<1,""CURRENT"","""")", _
"=IF(AND(1<=U7,U7<=60),""DELAYED"","""")", _
"=IF(AND(61<=U7,U7<=90),""SIGNIFICANTLY DELAYED"","""")", _
"=IF(U7>90,""CRITICAL"","""")")
' Value for status column is: "MGMT-" or "" followed by
' "CURRENT" or "DELAYED" or "SIGNIFICANTLY DELAYED" or "CRITICAL"
' The formulae above are placed in columns ColFrstNewBld to ColLastNewBld.
' There values are concatenated to create the status string
InxF = 0 ' the lower bound of a VBA.Array if always zero.
FormulaForSts = "=" ' Start formula for status column
For ColCrnt = ColCodeToNum(ColFrstNewBld) To ColCodeToNum(ColLastNewBld)
' For all used data rows, set cells in build columns to partial
' strings required for column "Status"
.Range(.Cells(RowDataFirst, ColCrnt), .Cells(LastRow, ColCrnt)).Value = Formulae(InxF)
' Concatenate A1 name for this cell to formula for status column
FormulaForSts = FormulaForSts & ColNumToCode(ColCrnt) & RowDataFirst
' If this is not the last column, add concatenate operator to formula
If ColCrnt < ColCodeToNum(ColLastNewBld) Then
FormulaForSts = FormulaForSts & "&"
End If
InxF = InxF + 1 ' Advance to next formula
Next
' The first build column contains "MGMT-" or ""
' The remaining columns contain one of "CURRENT" to "CRITICAL" depending
' on how late the task is.
' FormulaForSts concatenates the build columns into the required status
.Range(.Cells(RowDataFirst, ColSts), .Cells(LastRow, ColSts)).Value = FormulaForSts
With .Columns(ColFrstNewBld & ":" & ColLastNewBld)
.Font.Color = ClrWhite35 ' Use pale grey as font colour for build columns
End With
With .Columns(ColFrstNew & ":" & ColLastNew)
.EntireColumn.AutoFit
End With
End With
End Sub
Public Function ColCodeToNum(ByVal ColCode As String) As Long
' Checks ColCode is a valid column code for the version of Excel in use.
' If it is, it returns the equivalent column number.
' If it is not, it returns 0.
' 21Aug16 Coded
' 28Oct16 Renamed ColCode to match ColNum.
' ??????? Renamed from ColNum to provide a more helpful name.
Dim ChrCrnt As String
Dim ColCodeUc As String: ColCodeUc = UCase(ColCode)
Dim Pos As Long
ColCodeToNum = 0
For Pos = 1 To Len(ColCodeUc)
ChrCrnt = Mid(ColCodeUc, Pos, 1)
If ChrCrnt < "A" Or ChrCrnt > "Z" Then
Debug.Assert False ' Invalid column code
ColCodeToNum = 0
Exit Function
End If
ColCodeToNum = ColCodeToNum * 26 + Asc(ChrCrnt) - 64
Next
If ColCodeToNum < 1 Or ColCodeToNum > Columns.Count Then
Debug.Assert False ' Invalid column code
ColCodeToNum = 0
ColCodeToNum = 0
End If
End Function
Public Function ColNumToCode(ByVal ColNum As Long) As String
Dim ColCode As String
Dim PartNum As Long
' 3Feb12 Adapted to handle three character codes.
' ?????? Renamed from ColCode to create a more helpful name
If ColNum = 0 Then
ColNumToCode = "0"
Else
ColCode = ""
Do While ColNum > 0
PartNum = (ColNum - 1) Mod 26
ColCode = Chr(65 + PartNum) & ColCode
ColNum = (ColNum - PartNum - 1) \ 26
Loop
End If
ColNumToCode = ColCode
End Function
