Justplainj
Board Regular
- Joined
- Apr 15, 2021
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
Good day,
I have the following code which runs VBA from an already open workbook (Workbook1) to open several workbooks (Workbook2) one by one and paste a formula into cell Z2. I also need the formula to flash fill down.
I am getting 2 problems in the code.
In the code above which is in workbook 1, there are two points causing errors at Error 1 and Error 2.
Error 1 occurs after the workbook in the file path is opened (Workbook2), it enters the password as the workbook2 is encrypted.
The update links and update alerts as false correctly removes some of the dialog boxes however, because the links within workbook2 points to a different workbook it requests a encryption password of the workbook where the links are
I do not need to add a second password as the links will be automatically replaced with the code that will point to ranges within workbook2 itself.
The second error is when the macro in workbook1 tries to select the range("Z2") in workbook 2, it gives a runtime 1004 error.
I am also struggling with the code to flash fill the formula down after it is placed in range"Z2")
Any assistance will be appreciated.
Thanks
J
I have the following code which runs VBA from an already open workbook (Workbook1) to open several workbooks (Workbook2) one by one and paste a formula into cell Z2. I also need the formula to flash fill down.
I am getting 2 problems in the code.
VBA Code:
Option Explicit
Sub OpenRunCode() 'Open files run Excel VBA macro
Const sPath = "File\Path\" 'Real Path hidden
Dim sFil As String
Dim owb As Workbook
sFil = Dir(sPath & "*.xl*") 'Captures all XL files in a directory.
Do While sFil <> "" 'Loop through all files in Folder
Application.AskToUpdateLinks = False 'Stops update links from showing
Application.DisplayAlerts = False 'Stop alerts from showing
Set owb = Workbooks.Open(sPath & sFil, Password:="12345")
'Error 1
Range("Z2").Select ' Error 2
ActiveCell.FormulaR1C1 = _
"=IFERROR(LET(" & Chr(10) & "A,VLOOKUP(RC[-2],'Data Tab'!R[1]C[-14]:R[8]C[-10],2,FALSE)," & Chr(10) & "B,VLOOKUP(RC[-2],'Data Tab'!R[1]C[-8]:R[8]C[-4],2,FALSE)," & Chr(10) & "C,VLOOKUP(RC[-2],'Data Tab'!R[1]C[-2]:R[8]C[2],2,FALSE)," & Chr(10) & "IF(OR(RC[-3]=""A"",RC[-3]=""B Lower"",RC[-3]=""B Upper""),A," & Chr(10) & "IF(OR(RC[-3]=""C Lower"",RC[-3]=""C Upper"",RC[-3]=""D Lower""),B,C))" & Chr(10) & "),"""")"
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
sFil = Dir
Loop
End Sub
In the code above which is in workbook 1, there are two points causing errors at Error 1 and Error 2.
Error 1 occurs after the workbook in the file path is opened (Workbook2), it enters the password as the workbook2 is encrypted.
The update links and update alerts as false correctly removes some of the dialog boxes however, because the links within workbook2 points to a different workbook it requests a encryption password of the workbook where the links are
I do not need to add a second password as the links will be automatically replaced with the code that will point to ranges within workbook2 itself.
The second error is when the macro in workbook1 tries to select the range("Z2") in workbook 2, it gives a runtime 1004 error.
I am also struggling with the code to flash fill the formula down after it is placed in range"Z2")
Any assistance will be appreciated.
Thanks
J