What is wrong with my formula? (VBA)

[Cuylaerts]

Hello,

Formula :

Dim machines As String
Dim wk As Integer
Dim var As Integer
machines = "LB300"
wk = 1027
var = Application.WorksheetFunction.CountIfs(Sheets("Hoofd-Overzicht").Range("A:A"), "&wk&", Range("D:D"), "&machine&")
For x = 1 To ((Range("$F$3") - 1) / ActiveWorkbook.Worksheets("Ignore").Range("C20")) - (" &var& ")

i seem to get an error on the last part, after i entered the " -(" &var& ") " bit.
i can't seem to find out what the error is.
( the dim var formula is tested in the excel sheet and works)

Can anyone help me plz?

kind regards!

 

[Jonmo1]

Why are you putting the var variable in Quotes with & ?

Shouldn't that just be
For x = 1 To ((Range("$F$3") - 1) / ActiveWorkbook.Worksheets("Ignore").Range("C20")) - var

 

[Fluff]

Try

[B]For x = 1 To ((Range("$F$3") - 1) / ActiveWorkbook.Worksheets("Ignore").Range("C20")) - var[/B]

 

[Roxxien]

if you want to substract the value of your var variable, you aren't using the good syntax.

For x = 1 To ((Range("$F$3") - 1) / ActiveWorkbook.Worksheets("Ignore").Range("C20")) - var

You don't need to put anything around a variable. VBA automatically consider it as an Integer by declaring it at the start.

 

[Cuylaerts]

Because i was desperate . already tried with just " - var "
My error dissapears but then my code wont do what it has to do :

After the line its gonna copy rows. but the formula should check how many rows i already copied so it just copy the rows i need and not make duplicates.

maybe something wrong with my formula?

 

[Cuylaerts]

More details: if my cell value is 5 then im gonna copy the row 5 times.
Now if i change my value to 6, then my line should be copy once, because i already have the 5 other lines.
that's what the countifs is for , if i enter that formula in the sheet i get 5 as result.
so in the formula below that i'm trying to say that the amount of copys should be minus 5 , = 1
i hope this is a bit clear.

Ty