Hi
got a problem with a script when I change the destination, this works for “a1” but not for “b1”.
tell application "Microsoft Excel"
launch
--set screen updating to false
activate object worksheet "5Wk"
replace range "a:a" of worksheet active sheet what "1" replacement "1001" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "2" replacement "1002" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "3" replacement "1003" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "4" replacement "1004" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "5" replacement "1005" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "6" replacement "1006" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "7" replacement "1007" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "8" replacement "1008" search order by rows look at whole
replace range "a:a" of worksheet active sheet what "9" replacement "1009" search order by rows look at whole
activate object worksheet "5Wk"
sort range "a1" of worksheet "5Wk" key1 (range "a1" of worksheet "5Wk")
set columnToTest to range "a:a" of active sheet -- adjust
set maxvalueCutOff to 1000 -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=1/(rc" & (first column index of columnToTest) ¬
& "<" & maxvalueCutOff & ")"
set sourceRange to entire row of (special cells formulaCells type cell type formulas value errors)
activate object worksheet "Sheet2"
activate object range "a1"
set destRange to range "a1"
copy range sourceRange destination destRange
end tell
the results are
copy range entire row of range “‘[play bc49 test copy.xlsx]5Wk’!$E$19:$E$20” destination cell “a1”
but when I change the destination range to “b1” the results are
copy range entire row of range “‘[play bc49 test copy.xlsx]5Wk’!$E$19:$E$20” destination cell “b1”
“Microsoft Excel got an error: entire row of range "‘[play bc49 test copy.xlsx]5Wk’!$E$19:$E$20" doesn’t understand the copy range message.”
This does not make sense to me because the copy range is the same.
What am I missing here?
any help in understanding this would be appriciated
thanks
bills