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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Wilder1626

ASKER

This is perfect

Thanks for you help.