Concatenate Excel cells

Scripting Forums AppleScript | Mac OS X
,
#1

Hi all,

Long time Excel user, first time AppleScript programmer. I searched the forums but did not see any comparable solution.

I have a macro I pieced together at work, but some colleagues run Office 2008 on their Macs, which uses AppleScript. IT is not very willing to write AppleScript because they aren’t familiar with it, so I’m wondering if I could get some help to translate it.

Here’s the macro code:

Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer)
For Each cell In iRange
If cell.Value <> iLook Then
ConcatenateIf = ConcatenateIf & Chr$(10) & cell.Offset(0, iNum).Value
End If
Next cell
End Function

So I send a range of cells to this function. If the cell doesn’t equal iLook, then it adds the cell contents to a new row in the concatenated cell. Any help would be very much appreciated, thank you for your time.

#2

Hi there,

Not sure if this is what you’re trying to do:-

my ConcatenateIf("A1:A8", "iLook", "Workbook3", "sheet1", "E22")

on ConcatenateIf(iRange, iLook, thisWorkbook, thisWorksheet, concatCell)
	set conCat to ""
	tell application "Microsoft Excel"
		activate
		activate object worksheet thisWorksheet of workbook thisWorkbook
		set thisList to value of every cell of range iRange
		repeat with thisCell in thisList
			if (thisCell as text) ≠ iLook then
				set conCat to conCat & (thisCell as text) & return
			end if
		end repeat
		set value of cell (concatCell) to conCat
	end tell
end ConcatenateIf

Hopefully the params are pretty straight forward.

Regards,

Nick