there is likely a really simple solution to this but I haven’t been able to find it yet.
i am trying to define a range. the range varies from sheet to sheet so I need to use find the first and last columns and the first and last rows and then select the range knowing the index values of each of the corner columns.
I can get the numeric index of the columns and rows but to define the range properly ( i believe ) I also need the character index (e.g. the letter “M”)
i tried doing this using the intersect function but it turned out to be even more difficult when including variables
hope someone can shed some early morning light on this
cheers
Nic
tell application "Microsoft Excel"
activate
activate worksheet "sheet1"
set i to "13"
set j to "49"
get text of i
print "text of i is " & text of i
--set nRange to intersect range1 (column "e:m") range2 row "13:49"
select range ("e" & text of i & ":" & "m" & text of j)
end tell
tell application "Microsoft Excel"
set testCell to range "M13" of active sheet
set {tid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {"$"}}
set ColumnLetter to second text item of (get address of testCell)
set AppleScript's text item delimiters to tid
return ColumnLetter -- returns "M"
end tell
Given two ranges, return the range that has them on the corners
tell application "Microsoft Excel"
set topLeftCell to get range "F3" of active sheet
set bottomRightCell to get range "AH6:BJ9" of active sheet
set columnsCount to (first column index of bottomRightCell) - (first column index of topLeftCell) + (count of columns of bottomRightCell)
set rowsCount to (first row index of bottomRightCell) - (first row index of topLeftCell) + (count of rows of bottomRightCell)
set EnclosedRange to get resize topLeftCell row size rowsCount column size columnsCount
return EnclosedRange
end tell
-- returns range "[Workbook1]Sheet1!$F$3:$BJ$9" of application "Microsoft Excel"
Note: topLeftCell = A1:F10, bottomRightCell = B3:D4 will return A1:D4.
This is not the same as VBA’s Range([“A1:F10”], [“B3:D4”]) , which = A1:F10