hi! I am trying to add a formula in a sheet which requires the user to select a range of cells from a different file.
Both files might change name and format hence why I am using variable names.
I been braking my head and looking at old codes but I can't figure out what I am doing wrong. So any help would be amazing!!
Both files might change name and format hence why I am using variable names.
I been braking my head and looking at old codes but I can't figure out what I am doing wrong. So any help would be amazing!!
VBA Code:
Sub macro42()
Dim APOLD As Variant
Dim APWBOLD As Workbook
Dim APtEMPLATE As Variant 'open current AP (to be removed later)
Dim APWB As Workbook 'open current AP (to be removed later)
'OPEN previous APWBOLD
APOLD = Application.GetOpenFilename(fileFilter:="Excel Files (*.XLSx), *.XLSx", Title:="Select Previous version of AP")
If APOLD = False Then Exit Sub
Set APWBOLD = Workbooks.Open(APOLD)
'select ranges in old APWBOLD
Dim rngH As Range
Dim rngAN As Range
Dim rngData As Range
Dim DefaultRange As Range
'Get the headers range without the article number column
If TypeName(Selection) = "Range" Then
Set DefaultRange = Selection
Else
Exit Sub
End If
On Error Resume Next
Set rngH = Application.InputBox( _
Title:="Extract From Previous AP", _
Prompt:="Select a the headers range excluding the article number column", _
Default:=DefaultRange.Address, _
Type:=8)
On Error GoTo 0
'Test to ensure User Did not cancel
If rngH Is Nothing Then Exit Sub
'Get the aricle number column range without the header row
On Error Resume Next
Set rngAN = Application.InputBox( _
Title:="Extract From Previous AP", _
Prompt:="Select a the Article number range excluding the headers row", _
Default:=DefaultRange.Address, _
Type:=8)
On Error GoTo 0
'Test to ensure User Did not cancel
If rngAN Is Nothing Then Exit Sub
'Get the headers range without the article number column
On Error Resume Next
Set rngData = Application.InputBox( _
Title:="Extract From Previous AP", _
Prompt:="Select the data range excluding the headers row and the article number columns", _
Default:=DefaultRange.Address, _
Type:=8)
On Error GoTo 0
'Test to ensure User Did not cancel
If rngData Is Nothing Then Exit Sub
'open current AP (to be removed later)
APtEMPLATE = Application.GetOpenFilename(fileFilter:="Excel Files (*.XLSx), *.XLSx", Title:="Select Assortment NEW AP")
If APtEMPLATE = False Then Exit Sub
Set APWB = Workbooks.Open(APtEMPLATE)
'add formula in new AP
Application.Calculation = xlManual
APWB.Sheets("input").Range("BR12").Select
ActiveCell.Formula2R1C1 = _
"=IFERROR(XLOOKUP(R12C1,"rngH",XLOOKUP(R11C,"rngAN","rngData")),"""")"
With APWB.Sheets("input")
Range("BR12").Select
Selection.Copy
Range("BR12:DL13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With
With APWB.Sheets("input")
Range("BR12:DL13").AutoFill Destination:=Range("BR12:DL" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End With
Application.Calculation = xlAutomatic
'close old file
'APOLD.Close SaveChanges:=False
End Sub
"type miss-match". "Identifier under cursor not recognized" for the same line.. Am i maybe missing something else?
