Hi, can someone please advise what am I doing wrong here - when i run this code in excel it throws back an error saying 'No cells found' seems like code breaks at allranges(1).select
please note: this used to work fine before but since excel updated from 2010 to 2016. this issue has occurred. Thanks in advance!
Code im running is below:
'Recalculate/Resize Array after
Dim addIn As COMAddIn
Dim automationObject As Object
Set addIn = Application.COMAddIns("PI DataLink")
Set automationObject = addIn.Object
Dim allRanges(1 To 11) As Range
Dim i As Long
Set allRanges(1) = Range(theRange)
'For each DataLink array, select the top left cell, then select all cells in the array, and finally resize the array.
allRanges(1).Select
automationObject.ResizeRange
Worksheets("Alarms").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("AlarmsDataDump").Activate
'Copy the Array as Hard Values so they can be manipulated for alarms
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Columns("A:B").Select
Selection.Copy
Sheets("TempData").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AlarmsDataDump").Select
Columns("A:L").Select
Application.CutCopyMode = False
' Selection.ClearContents
Sheets("TempData").Select
Selection.Cut
Sheets("AlarmsDataDump").Select
Columns("A:B").Select
ActiveSheet.Paste
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
please note: this used to work fine before but since excel updated from 2010 to 2016. this issue has occurred. Thanks in advance!
Code im running is below:
'Recalculate/Resize Array after
Dim addIn As COMAddIn
Dim automationObject As Object
Set addIn = Application.COMAddIns("PI DataLink")
Set automationObject = addIn.Object
Dim allRanges(1 To 11) As Range
Dim i As Long
Set allRanges(1) = Range(theRange)
'For each DataLink array, select the top left cell, then select all cells in the array, and finally resize the array.
allRanges(1).Select
automationObject.ResizeRange
Worksheets("Alarms").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("AlarmsDataDump").Activate
'Copy the Array as Hard Values so they can be manipulated for alarms
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Columns("A:B").Select
Selection.Copy
Sheets("TempData").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AlarmsDataDump").Select
Columns("A:L").Select
Application.CutCopyMode = False
' Selection.ClearContents
Sheets("TempData").Select
Selection.Cut
Sheets("AlarmsDataDump").Select
Columns("A:B").Select
ActiveSheet.Paste
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual