Add a formula when transfer from Grid to excel

Wilder1626
Wilder1626 used Ask the Experts™
on
Hello,

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:
=NB.SI(A:A,D3")

Open in new window


How can i do that?

Thanks


    '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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Wilder1626

Author

Commented:
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.
Wilder1626

Author

Commented:
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?
Rory Archibald
Most Valuable Expert 2011
Top Expert 2011

Commented:
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!

Find out now!
Wilder1626

Author

Commented:
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
Commented:
Yes but you need to double up the quotes round the text:
Range("E3").Formula = "=COUNTIF($A:$A, ""Text im looking for"")"
Wilder1626

Author

Commented:
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