Target formula with a code

[asyamonique]

{=IF(ISNA(MATCH(C32,C$5:C$24,0)),"",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))}
{=IF(ROWS(C$32:C32)<=C$41,SMALL($C$5:$C$24,ROWS(C$32:C32)),"")}
{=IF(ISNA(MATCH(E32,C$5:C$24,0)),"",INDEX(A$5:A$24,MATCH(E32,C$5:C$24,0)))}
{=IF(ROWS(E$32:E32)<=E$41,LARGE($C$5:$C$24,ROWS(E$32:E32)),"")}

How can i target those formulas with the codes like below?

 Sheet1.Cells(32, 2).Resize(9, 1).Formula =.........

 Sheet1.Cells(32, 3).Resize(9, 1).Formula =.........

 Sheet1.Cells(32, 4).Resize(9, 1).Formula =.........

 Sheet1.Cells(32, 5).Resize(9, 1).Formula =.........

Many Thanks

 

[Marcelo Branco]

Hi,

For the first formula, try this

Sheet1.Cells(32, 2).Resize(9, 1).Formula = "=IF(ISNA(MATCH(C32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"

The same for other formulas (observe the double quotes """"" inside the formula-string)

HTH

M.

 

[Peter_SSs]

Perhaps this is what you mean?

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> Sheet1<br>    .Cells(32, 2).FormulaArray = _<br>        "=IF(ISNA(MATCH(C32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"<br>    .Cells(32, 3).FormulaArray = _<br>        "=IF(ROWS(C$32:C32)<=C$41,SMALL($C$5:$C$24,ROWS(C$32:C32)),"""")"<br>    .Cells(32, 4).FormulaArray = _<br>        "=IF(ISNA(MATCH(E32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(E32,C$5:C$24,0)))"<br>    .Cells(32, 4).FormulaArray = _<br>        "=IF(ROWS(E$32:E32)<=E$41,LARGE($C$5:$C$24,ROWS(E$32:E32)),"""")"<br>    .Cells(32, 2).Resize(9, 4).FillDown<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

 

[asyamonique]

Perhaps this is what you mean?

With Sheet1
.Cells(32, 2).FormulaArray = _
"=IF(ISNA(MATCH(C32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"
.Cells(32, 3).FormulaArray = _
"=IF(ROWS(C$32:C32)<=C$41,SMALL($C$5:$C$24,ROWS(C$32:C32)),"""")"
.Cells(32, 4).FormulaArray = _
"=IF(ISNA(MATCH(E32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(E32,C$5:C$24,0)))"
.Cells(32, 4).FormulaArray = _
"=IF(ROWS(E$32:E32)<=E$41,LARGE($C$5:$C$24,ROWS(E$32:E32)),"""")"
.Cells(32, 2).Resize(9, 4).FillDown
End With

With Sheet1
    .Cells(32, 2).FormulaArray = _
        "=IF(ISNA(MATCH(C32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"
    .Cells(32, 3).FormulaArray = _
        "=IF(ROWS(C$32:C32)<=C$41,SMALL($C$5:$C$24,ROWS(C$32:C32)),"""")"
    .Cells(32, 4).FormulaArray = _
        "=IF(ISNA(MATCH(E32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(E32,C$5:C$24,0)))"
    .Cells(32, 5).FormulaArray = _
        "=IF(ROWS(E$32:E32)<=E$41,LARGE($C$5:$C$24,ROWS(E$32:E32)),"""")"
    .Cells(32, 2).Resize(9, 4).FillDown
End With

Thats Great
Thanks Peter

 

[asyamonique]

Perhaps this is what you mean?

With Sheet1
.Cells(32, 2).FormulaArray = _
"=IF(ISNA(MATCH(C32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"
.Cells(32, 3).FormulaArray = _
"=IF(ROWS(C$32:C32)<=C$41,SMALL($C$5:$C$24,ROWS(C$32:C32)),"""")"
.Cells(32, 4).FormulaArray = _
"=IF(ISNA(MATCH(E32,C$5:C$24,0)),"""",INDEX(A$5:A$24,MATCH(E32,C$5:C$24,0)))"
.Cells(32, 4).FormulaArray = _
"=IF(ROWS(E$32:E32)<=E$41,LARGE($C$5:$C$24,ROWS(E$32:E32)),"""")"
.Cells(32, 2).Resize(9, 4).FillDown
End With

{=COUNTIF(C5:C24,"<0")}
{=COUNTIF(C5:C24,">0")}

Peter im trynig the same way for the formulas giving above.It is giving error!
Do i miss something?
For the first formula range should be C41
For the SECOND formula range should be E41
Cheers

 

[Peter_SSs]

{=COUNTIF(C5:C24,"<0")}
{=COUNTIF(C5:C24,">0")}

Peter im trynig the same way for the formulas giving above.It is giving error!
Do i miss something?
For the first formula range should be C41
For the SECOND formula range should be E41
Cheers

1. Why are you using array formulas? It seems you are simply trying to count cells in a range with a given condition.

2. Please post the whole code that you are using for this new task.

3. What is the error?
- Is the code giving an error? If so, what is the error message and what line is causing the error when you 'Debug'?
- Or is it the formula returning an error? If so, what error?