I’m writing quite a lengthy script to combine the data from three spreadsheets. It’s going fairly well so far, but I’m stuck on a little bit that I’ll probably end up kicking myself for…
Column E Header: User ID
Column F Header: DisplayName
I’ve entered the following formula for cell F2 (which is a vlookup of the contents of cell E2 against the ‘AD users’ spreadsheet)
$sheet.cells.item(2,6).Formula = ('=VLOOKUP(E2,[ADUsers.csv]ADUsers!$A$2:$B$3291,2,false')
It works fine.
However, I don’t want to use a fixed value in my script for the search area in the second spreadsheet (i.e. $A$2:$B$3291) as the number of rows (which represent AD accounts) change on a daily basis. I suppose I could substitute 10000 or 20000 for the $B value, but I was wondering if there was a way of specifying just the populated cells in that column?
Also - I need to copy that formula down so that it goes all the way down column F (I would ordinarily double-click the tiny black square in the bottom right corner of the cell to copy down the formula using the GUI). I’ve tried messing with the ‘EntireColumn’ property, but that interferes with the column heading.