Insert Formula with VBA

[methode2404]

Hi,

I will insert this formula to cell with VBA

=COUNTIFS('today'!C:C;OOS_daily!G1;'today'!G:G;0;'today'!D:D;"<>N";'today'!O:O;0;'today'!P:P;">0")

I try this;

Sub test()
'
' test Macro
'


'
Columns("G:G").Select
ActiveSheet.Range("$G$1:$G$50000").RemoveDuplicates Columns:=1, Header:=xlNo
Range("H1").Select
Worksheets("OSS_daily").Range("H1").Formula = "=COUNTIFS('today'!C:C;OOS_daily!G1;'today'!G:G;0;'today'!D:D;""<>N"";'today'!O:O;0;'today'!P:P;"">0"")"

End Sub

I get the error message : run-time error '9': (for the last line)

can someone tell me where the problem is ?

Best Regards
Metin

 

[Fluff]

Do you have a sheet called OSS_daily? or should it be OOS_daily?

 

[methode2404]

I have sheet with name OOS_daily , it's my activesheet

 

[methode2404]

I have changed the error in formula;

Columns("G:G").Select
ActiveSheet.Range("$G$1:$G$50000").RemoveDuplicates Columns:=1, Header:=xlNo
Range("H1").Select

Worksheets("OOS_daily").Range("H1").Formula = "=COUNTIFS('today'!C:C;OOS_daily!G1;'today'!G:G;0;'today'!D:D;""<>N"";'today'!O:O;0;'today'!P:P;"">0"")"

Bu i get now; run-time error '1004' application-defined or object-defined error

 

[Fluff]

Try changing the semi-colons in the formula to commas.

 

[methode2404]

That's it , you're the best.
Many thanks

 

[methode2404]

Another quick questions,


With this part of code, i delete all cells that contains "0"


For Each cell In Range("G1:H1000")
If cell.Value = "0" Then cell.Clear
Next

The zero's are in column H
In Column G are the barcode number

When its deleted the zero in H, it's should delete the left cell too.

Is that possible ?

 

[MARK858]

Try...

    Dim mycell As Range
    For Each mycell In Range("H1:H1000")
        If mycell.Value = 0 Then mycell.Offset(, -1).Resize(1, 2).Clear
    Next

 

[methode2404]

Many thanks, works fine