CODEWITHSUNDEEP
vbaexcelexcel-formula
Swagayema
Swagayema

Reputation: 117

Insert formula with Variable VBA

I'm trying to insert a formula in the Column C of Sheet2. The row number however depends on the value of pasterow. The range in the formula should be G[lastrow]:NS[lastrow] with lastrow pertaining to the last row of Sheet1.

Here is my code:

Sub try()

With Sheets("Sheet2")

pasterow = .Cells(.rows.Count, "B").End(xlUp).Offset(1, 0).Row

With Sheets("Sheet1")
    lastRow = ActiveWorkbook.Worksheets("Sheet1").Range("F" & .rows.Count).End(xlUp).Row

    ActiveWorkbook.Worksheets("Sheet2").Range("C" & pasterow).formula = _
    "=COUNTIF(Sheet1!G & lastRow & :NS & lastRow & , ""VL"" )"

End With
End With

End Sub

I'll really appreciate if someone could point out what's wrong with my code. Thanks!

Upvotes: 0

Views: 5923

Answers (3)

BOB
BOB

Reputation: 700

Try with: 

ActiveWorkbook.Worksheets("Sheet2").Range("C" & pasterow).formula = _  
"=COUNTIF(Sheet1!R" & lastrow & "C7:R" & lastrow & "C383,""VL"")"

Upvotes: 0

Linga
Linga

Reputation: 955

Try this..

ActiveWorkbook.Worksheets("Sheet2").Range("C" & pasterow).FormulaR1C1 = "=COUNTIF(Sheet1!C[4]:C[380],""VL"")"

May be this is what your expecting to achieve!

 ActiveWorkbook.Worksheets("Sheet2").Range("C" & pasterow).Value = "=COUNTIF(Sheet1!G" & lastRow & ":NS" & lastRow & ", ""VL"" )"

Upvotes: 0

steegness
steegness

Reputation: 459

The VBA variables are inside your formula string. Compare:

ActiveWorkbook.Worksheets("Sheet2").Range("C" & pasterow).formula = "=COUNTIF(Sheet1!G" & lastrow & ":NS" & lastrow & ", ""VL"" )"

Upvotes: 1

Related Questions