
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:
How can i do that?
Thanks
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")
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

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?
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.Ac tiveSheet. Range("E3" ).FormulaL ocal = "=NB.SI($A:$A,D3 )"
or convert to US English:
xlObject.ActiveWorkbook.Ac tiveSheet. Range("E3" ).Formula = "=COUNTIF($A:$A,D3 )"
xlObject.ActiveWorkbook.Ac
or convert to US English:
xlObject.ActiveWorkbook.Ac

ASKER
Hello rorya
Both are working.
But quick question,
Can i change it for something like this:
xlObject.ActiveWorkbook.Ac tiveSheet. Range("E3" ).Formula = "=COUNTIF($A:$A, "Text im looking for")"
Instead of the cell address.
Thanks again for your help
Both are working.
But quick question,
Can i change it for something like this:
xlObject.ActiveWorkbook.Ac
Instead of the cell address.
Thanks again for your help
ASKER CERTIFIED SOLUTION
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.

ASKER
This is perfect
Thanks for you help.
Thanks for you help.
ASKER
Open in new window
But the result is: #NOM?. But if i double click in the cell, the result is now showing.