Link to home
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Add a formula when transfer from Grid to excel


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

Avatar of Wilder1626
Flag of Canada image


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

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
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
This is perfect

Thanks for you help.