how to get character index of a cell eg. "M" of cell "M13"

Hi folks,

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



tell application "Microsoft Excel"
	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

To get the column letters of a range

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

thanks Mike!

(i’ll be having a look at these shortly)