asked on

Add a formula when transfer from Grid to excel

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

Wilder1626

ASKER

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

ASKER

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

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 )"

Wilder1626

ASKER

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

ASKER CERTIFIED SOLUTION

Rory Archibald

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Wilder1626

ASKER

This is perfect

Thanks for you help.