CODEWITHSUNDEEP
excelvbaformula
Rob Davidowitz
Rob Davidowitz

Reputation: 3

Using correct quotation marks in formula code

I want to use 2 variables of 2 lines as part of a formula to be entered into Excel. I keep getting a syntax error which makes me think I have the quotation marks all in the wrong places.

I have tried every single permutation for quotation marks with no success.

Worksheets("Sheet1").Range("O" & Week_LastLine).Formula = "=MIN("I" & Week_TopLine & "":"" & "I" & Week_LastLine)+5000"

I need the formula =Min(I10..I20)+5000 to be entered where the 10 and 20 are derived from variables. How can this be achieved?

Upvotes: 0

Views: 55

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

Correctly stringing items together can sometimes be tricky. In this case, the answer would be:

"=MIN(I" & Week_TopLine & ":I" & Week_LastLine & ")+5000"

Sometimes I solve the problem as follows. Our starting point is:

"=Min(I10:I20)+5000"

Since we need to replace the 10 and 20 with variables, I isolate them first:

"=Min(I" & "10" & ":I" & "20" & ")+5000"

At this point the two strings still give the same result. Now simply replace the "10" and "20" with your variables.

Upvotes: 2

Related Questions