neveraskedforthis
New Member
- Joined
- Mar 15, 2019
- Messages
- 1
Hello,
I'm having issues with inputting a formula array into a range of cells. The code in question is as follows:
I'm not sure i'm going wrong. Initially i didn't have the replace function, as further up in my code, i have the following code which works fine:
The only difference is the small loop in the first example so it will add the formula to various rows. but for some reason this iteration of the code doesn't work. I tried the replace function incase it was a character limit issue. Anybody have any ideas? would appreciate any help on the issue
I'm having issues with inputting a formula array into a range of cells. The code in question is as follows:
Code:
theformulapart1 = "=IFNA(INDEX('" & ws & "'!$" & AllelCol & "$1:$" & AllelCol & "$1000" & "X_X_X())"
theformulapart2 = ",MATCH('Allele summary'!$C" & L & "&" & CL & "3" & ",'" & ws & "'!$" & genetypeletter & "$1:$" & genetypeletter & "$1000&'" & ws & "'!$" & HelperCLletter & "$1:$" & HelperCLletter & "$1000,0)),0)"
Sheets("Allele summary").Activate
For K = 0 To 19
L = X + K
Range(CL & L).FormulaArray = theformulapart1
Range(CL & L).Replace "X_X_X())", theformulapart2
CLlastNum = i + lastrow
CLlastLetter = Split(Cells(1, CLlastNum + 1).Address, "$")(1)
Range(CL & L).Select
Selection.AutoFill Destination:=Range(CL & L & ":" & CLlastLetter & L), Type:=xlFillDefault
Next K
I'm not sure i'm going wrong. Initially i didn't have the replace function, as further up in my code, i have the following code which works fine:
Code:
Sheets("Allele summary").Activate
Range(CL & X).FormulaArray = "=IFNA(INDEX('" & ws & "'!$" & AllelCol & "$1:$" & AllelCol & "$1000,MATCH('Allele summary'!$C" & X & "&" & CL & "3" & ",'" & ws & "'!$" & genetypeletter & "$1:$" & genetypeletter & "$1000&'" & ws & "'!$" & HelperCLletter & "$1:$" & HelperCLletter & "$1000,0)),0)"
CLlastNum = i + lastrow
CLlastLetter = Split(Cells(1, CLlastNum + 1).Address, "$")(1)
Range(CL & X).Select
Selection.AutoFill Destination:=Range(CL & X & ":" & CLlastLetter & X), Type:=xlFillDefault