How to insert formula in VBA?

[Kishan]

Hello to all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a formula shown below in cell Q3 and copied it down to cell Q59051 and it inserts with control + shift + enter keys. <o></o>

=MAX(FREQUENCY(IF($B3:$O3=1,COLUMN($B3:$O3)),IF($B3:$O3<>1,COLUMN($B3:$O3))))

<o></o>
<o></o>
Is it possible to use this formula placing it in the VBA and run the code from cell Q3 to cell Q59051 and giving the result only in the cells? <o></o>
<o></o>
Thanks in advance,<o></o>
Kishan<o></o>

 

[bjorgen]

Hello to all,

I have a formula shown below in cell Q3 and copied it down to cell Q59051 and it inserts with control + shift + enter keys.
Code:

=MAX(FREQUENCY(IF($B3:$O3=1,COLUMN($B3:$O3)),IF($B3:$O3<>1,COLUMN($B3:$O3))))</pre>


Is it possible to use this formula placing it in the VBA and run the code from cell Q3 to cell Q59051 and giving the result only in the cells?

Thanks in advance,
Kishan

Is this what you wanted, to copy and paste the values after running the formula?

sub NameHere()

    range("q3:q59051").select
    selection.formula = "=MAX(FREQUENCY(IF($B3:$O3=1,COLUMN($B3:$O3)),IF($B3:$O3<>1,COLUMN($B3:$O3))))"
    selection.copy
    selection.pastespecial paste:=xlPasteValues

end sub

 

[Yahya]

the best way to record macro

 

[shg]

One way:

Sub k()
    With Range("Q3:Q59050")
        .Cells(1).FormulaArray = "=MAX(FREQUENCY(IF($B3:$O3=1, COLUMN($B3:$O3)), IF($B3:$O3<>1, COLUMN($B3:$O3))))"
        .Cells(1).Copy .Offset(1)
        .Value = .Value
    End With
End Sub

 

[Kishan]

Bjotgen, your code produces this error: #¡VALUE! Anyway, thank you for your collaboration. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Shg, your 5 line code has reduced the workbook size nearly 6.7MB. Now the workbook opens and closes quickly. <o></o>
<o></o>
Really, I appreciate your help for giving me the code and saving my time while working with long data’s.<o></o>
<o></o>
Regards,<o></o>
Kishan

 

[shg]

You're welcome, glad it worked for you.

 

[Kishan]

Shg, I have come up with a new problem. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The code below counts the frequency of ‘1’ which is working perfectly.<o></o>

=MAX(FREQUENCY(IF($B3:$O3=1,COLUMN($B3:$O3)),IF($B3:$O3<>1,COLUMN($B3:$O3))))

<o></o>
<o></o>
But when I change the value ‘1’ to ‘X’ for counting the frequency of ‘X’ and try to run the code it does not work. It also highlights in red.<o></o>

=MAX(FREQUENCY(IF($B3:$O3="X",COLUMN($B3:$O3)),IF($B3:$O3<>"X",COLUMN($B3:$O3))))

<o></o>
<o></o>
Please, will you look in this situation how the code should be written in a correct way. Or is there another code to count the frequency of X?<o></o>
<o></o>
Kishan.

 

[shg]

Double up the quotation marks.

 

[Kishan]

Shg, you are great!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you once again for your quick solution.<o></o>
<o></o>
Regards,<o></o>
Kishan.<o></o>