Help referencing different workbook when calling literal Excel function

[SliderJeff]

Hey guys,

Thanks to several users here, I was able to add a function into my VBA which calculated the STDEV.P on a bunch of data (977k rows in a single column). However, I now need to change this code to calculate STDEV.P on data located in a workbook called "inputWorkbook" and store it into a cell located in a workbook called "outputWorkbook".

What I have currently is writing the data into the correct cell within outputWorkbook, but it's performing the STDEV.P on the data in that same workbook instead of the inputWorkbook like I need it to.

Here's what I have:

' Find Row with last value in it
LR = inputWorkbook.Worksheets(fileName).Cells(inputWorkbook.Worksheets(fileName).Rows.count, "D").End(xlUp).Row

outputWorkbook.Worksheets("Sheet1").Cells(10 + fileNum * FILE_OFFSET, 3).FormulaArray = "=STDEV.P(IF(B2:B" & LR & "<>0,B2:B" & LR & "))"

How do I modify the right hand side of the equation such that I can call this literal Excel function version of STDEV.P on the data located in inputWorkbook.Worksheets("Sheet1")?

My Googling didn't yield much in the way of showing people calling literal Excel functions instead of the VBA versions from one Workbook to another. It could be that I didn't know that right keywords to use to get at the info I needed, though.

Thanks for any help you can provide.

Regards,
Jeff

 

[SliderJeff]

Ok, I'm still hitting stumbling blocks here. I tried the following and keep getting "Compile error, Expected: end of statement" and it highlights the "B2" portion of the code below:

outputWorkbook.Worksheets("Sheet1").Cells(10 + fileNum * FILE_OFFSET, 3).FormulaArray = "=STDEV.P(IF(inputWorkbook.Worksheets(fileName).Range("B2:B")" & LR & "<>0,inputWorkbook.Worksheets(fileName).Range("B2:B")" & LR & "))"

I've tried messing around with using the exclamation mark before "B2:B" ala this example code:

Range("K10").FormulaArray = "=STDEVP(IF('[Book1.xlsx]Sheet1'!B2:B" & LR & "<>0,'[Book1.xlsx]Sheet1'!B2:B" & LR & "))"

but that resulted in a different "run-time 1004 error: Unable to set the FormulaArray property of Range class" issue. I also tried using Columns("B2:B") instead of Range, and got the same compiler error as when I used Range.

Appreciate any help.

Regards,
Jeff


Thanks,
Jeff