jessebh2003
Board Regular
- Joined
- Feb 28, 2020
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
I have two macros each with an error.
The first macro has a very long formula that works correctly for me but when others run the macro, they get an error message. Here's the formula:
In the VBA, the code is structured:
We tried connecting all of the rows of code into one long row but got the same error.
The second macro replaces merge fields in Word with data from Excel, which all works correctly until the macro gets to the code to save the Word document.
The code works find for me but they get the error:
"Run-time error '5152':
This is not a valid file name.
Try one or more of the following:
*Check the path to make sure it was typed correctly.
*Select a file from the list of files and folders."
Can someone help me solve these two errors? Thanks!!!
The first macro has a very long formula that works correctly for me but when others run the macro, they get an error message. Here's the formula:
Excel Formula:
=COUNT(FILTER(ROW(Data!$AQ$3:$AQ$1000),(Data!$AQ$3:$AQ$1000="Yes")+(Data!$AQ$3:$AQ$1000="No")+(Data!$BA$3:$BA$1000="Yes")+(Data!$BA$3:$BA$1000="No")+(Data!$BK$3:$BK$1000="Yes")+(Data!$BK$3:$BK$1000="No")+(Data!$BU$3:$BU$1000="Yes")+(Data!$BU$3:$BU$1000="No")+(Data!$CE$3:$CE$1000="Yes")+(Data!$CE$3:$CE$1000="No")+(Data!$CO$3:$CO$1000="Yes")+(Data!$CO$3:$CO$1000="No")+(Data!$CY$3:$CY$1000="Yes")+(Data!$CY$3:$CY$1000="No")+(Data!$DI$3:$DI$1000="Yes")+(Data!$DI$3:$DI$1000="No")+(Data!$DS$3:$DS$1000="Yes")+(Data!$DS$3:$DS$1000="No")+(Data!$EC$3:$EC$1000="Yes")+(Data!$EC$3:$EC$1000="No")+(Data!$EN$3:$EN$1000="Yes")+(Data!$EN$3:$EN$1000="No")))
In the VBA, the code is structured:
VBA Code:
Range("J9").Select
ActiveCell.FormulaR1C1 = "=COUNT(FILTER(ROW(Data!R3C43:R1000C43),(Data!R3C43:R1000C43=""Yes"")+(Data!R3C43:R1000C43=""No"")" & _
"+(Data!R3C53:R1000C53=""Yes"")+(Data!R3C53:R1000C53=""No"")+(Data!R3C63:R1000C63=""Yes"")+(Data!R3C63:R1000C63=""No"")" & _
"+(Data!R3C73:R1000C73=""Yes"")+(Data!R3C73:R1000C73=""No"")+(Data!R3C83:R1000C83=""Yes"")+(Data!R3C83:R1000C83=""No"")" & _
"+(Data!R3C93:R1000C93=""Yes"")+(Data!R3C93:R1000C93=""No"")+(Data!R3C103:R1000C103=""Yes"")+(Data!R3C103:R1000C103=""No"")" & _
"+(Data!R3C113:R1000C113=""Yes"")+(Data!R3C113:R1000C113=""No"")+(Data!R3C123:R1000C123=""Yes"")+(Data!R3C123:R1000C123=""No"")" & _
"+(Data!R3C133:R1000C133=""Yes"")+(Data!R3C133:R1000C133=""No"")+(Data!R3C144:R1000C144=""Yes"")+(Data!R3C144:R1000C144=""No"")))"
We tried connecting all of the rows of code into one long row but got the same error.
The second macro replaces merge fields in Word with data from Excel, which all works correctly until the macro gets to the code to save the Word document.
VBA Code:
.SaveAs2 Environ("userprofile") & "\Desktop\ROL Evaluation Report" & "_" & Sheets("Data").Range("B1").Value _
& "_" & Format(Now, "yyyy-mm-dd hh-mm") & ".docx"
The code works find for me but they get the error:
"Run-time error '5152':
This is not a valid file name.
Try one or more of the following:
*Check the path to make sure it was typed correctly.
*Select a file from the list of files and folders."
Can someone help me solve these two errors? Thanks!!!