Add a formula when transfer from Grid to excel

Wilder1626 used Ask the Experts™

I have a VB6 code that i use to export from an MSHFlexgrid1 to Excel.

Now, what i want to do it to add a formula in cell "E3" that will look like this:

Open in new window

How can i do that?


    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add
    Clipboard.Clear 'Clear the Clipboard
    With Form29.MSHFlexGrid1
        'Select Full Contents (You could also select partial content)
        .Col = 0               'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard

    End With
    With xlObject.ActiveWorkbook.ActiveSheet
   xlObject.ActiveWorkbook.ActiveSheet.Range("a1") = Form29.name_holyday.Text & " " & "Holiday"
    xlObject.ActiveWorkbook.ActiveSheet.Range("a1").Font.Bold = True
    xlObject.ActiveWorkbook.ActiveSheet.Range("a1").Font.Size = 17
    '205-197-191 = gris
    xlObject.ActiveWorkbook.ActiveSheet.Range("A6:S6").Interior.Color = RGB(205, 197, 191)
    xlObject.ActiveWorkbook.ActiveSheet.Range("a3") = "Date od this report:"
    xlObject.ActiveWorkbook.ActiveSheet.Range("a3").Font.Bold = True
    xlObject.ActiveWorkbook.ActiveSheet.Range("B3") = Format(Date, "mmm dd, yyyy")
    xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Font.Bold = True
    xlObject.ActiveWorkbook.ActiveSheet.Range("D3") = "Total in lane:"
    xlObject.ActiveWorkbook.ActiveSheet.Range("D3").Font.Bold = True

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I just tried that:
xlObject.ActiveWorkbook.ActiveSheet.Range("E3").Formula = "=NB.SI($A:$A,D3 )"

Open in new window

But the result is: #NOM?.  But if i double click in the cell, the result is now showing.
Quick question.

It would be better if in Cell E5 i would have the number of time "Total in lane:  " would be in column A.

His that possible?
Most Valuable Expert 2011
Top Expert 2011

Try using FormulaLocal:

xlObject.ActiveWorkbook.ActiveSheet.Range("E3").FormulaLocal = "=NB.SI($A:$A,D3 )"

or convert to US English:

xlObject.ActiveWorkbook.ActiveSheet.Range("E3").Formula = "=COUNTIF($A:$A,D3 )"

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hello rorya

Both are working.

But quick question,

Can i change it for something like this:
xlObject.ActiveWorkbook.ActiveSheet.Range("E3").Formula = "=COUNTIF($A:$A, "Text im looking for")"

Instead of the cell address.

Thanks again for your help
Most Valuable Expert 2011
Top Expert 2011
Yes but you need to double up the quotes round the text:
Range("E3").Formula = "=COUNTIF($A:$A, ""Text im looking for"")"
This is perfect

Thanks for you help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial