dabug80
asked on
Excel Macro 2 x Dim declarations?
Hello,
I'm trying to define two names via the dim function. These names are being defined for two different non-active sheets.
When I run the macro below I get the message:
Duplicate declaration in current scope
- with the error highlighting row 11.
What's the best way of defining these names for non active sheets?
I'm trying to define two names via the dim function. These names are being defined for two different non-active sheets.
When I run the macro below I get the message:
Duplicate declaration in current scope
- with the error highlighting row 11.
What's the best way of defining these names for non active sheets?
Sub mac_quickformulas()
'
' mac_quickformulas Macro
'
Dim uniqueIDformulas As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Unique ID")
uniqueIDformulas = ws.Range("B" & Rows.Count).End(xlUp).Row
Dim dataformulas As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")
dataformulas = ws.Range("B" & Rows.Count).End(xlUp).Row
'Add forumula columns to the Unique ID Sheet
Sheets("Unique ID").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Data!C[-11],"">0"",Data!C[-6],'Unique ID'!RC[-5],Data!C[-9],"">0"")"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Data!C[-12],"">0"",Data!C[-7],'Unique ID'!RC[-6],Data!C[-10],"""")"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=(INDEX('Australia Post Codes'!C[-10],MATCH('Unique ID'!RC[-5],'Australia Post Codes'!C[-12],0)))"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Region"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Jobs"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Quotes"
Range("M2").Select
Range("L2:N2").AutoFill Destination:=Range("L2:N" & uniqueIDformulas)
'Add formula columns to the Data Sheet
Sheets("Data").Select
Range("K1").Select
ActiveWindow.SmallScroll Down:=-6
ActiveCell.FormulaR1C1 = "Region"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=(INDEX('Australia Post Codes'!C[-7],MATCH(RC[-3],'Australia Post Codes'!C[-9],0)))"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=(INDEX('Unique ID'!C[-11],MATCH(Data!RC[-6],'Unique ID'!C[-5],0)))"
Range("L1").Select
ActiveCell.FormulaR1C1 = "ID"
Range("L2").Select
Range("K2:L2").AutoFill Destination:=Range("K2:L" & dataformulas)
Sheets("Closed Address Formatting").Select
Range("E6").Select
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.