VBA Formula with Custom Variables Syntax Help

[Legacy 185734]

Hey All,

I'm normally pretty good with most programming, but I cant seem to figure out what is wrong with my formula in VBA. I've tried a few things/different variations so apologies if the formula is now completely messed up!

Dim columnNumber As Integer
Dim columnNumber1 As Integer
Dim columnNumber2 As Integer

Dim columnLetter As String
Dim columnLetter1 As String
Dim columnLetter2 As String

columnNumber = 33 'AG - Fixed row number for vlookups
columnNumber1 = 33 'AG
columnNumber2 = 18 'R

columnLetter = Left(Cells(1, columnNumber).Address(1, 0), InStr(1, Cells(1, columnNumber).Address(1, 0), "$") - 1)
columnLetter1 = Left(Cells(1, columnNumber1).Address(1, 0), InStr(1, Cells(1, columnNumber1).Address(1, 0), "$") - 1)
columnLetter2 = Left(Cells(1, columnNumber2).Address(1, 0), InStr(1, Cells(1, columnNumber2).Address(1, 0), "$") - 1)

Range(columnLetter1 + "4").Select
ActiveCell.Formula = _
    "=IFERROR(IF(" & columnLetter1 + 7 & "/(" &  columnLetter1 + 5 & "*oee)"LESS THAN"VLOOKUP(" & columnLetter + 1 &  ",LineHours,4,FALSE)*'Look Up'!" & columnLetter2 + 5 & ","  & columnLetter1 + 7 & "/(" & columnLetter1 + 5 &  "*oee),VLOOKUP(" & columnLetter + 1 & ",LineHours,4,FALSE)*'Look  Up'!" & columnLetter2 + 5 & "),"")"

So yea, in this case, the formula should look like this:

=IFERROR(IF(AG7/(AG5*oee)"LESS THAN"VLOOKUP(AG1,LineHours,4,FALSE)*'Look  Up'!R5,AG7/(AG5*oee),VLOOKUP(AG1,LineHours,4,FALSE)*'Look  Up'!R5),"")

Now when I debug I can see that the formula is correct in what I want it to look like, but I'm getting an error.

Please note that when entering code, it kept ignoring everything after a "<", so I have replaced "<" with ""LESS THAN"". In the actual code there is a "<" (without quotations).

Hope someone can clear the syntax issue or whatever it is I'm doing wrong.

Cheers.

 

[CharlesChuckieCharles]

What do you think
"columnLetter1 + 7" is
since columnLetter1 is a string?

 

[Legacy 185734]

Yea, maybe I should have explained that - I just figured you'd know that I've already changed it and left it like that on purpose - apparently not.

Well I changed it to be like this:

ActiveCell.Formula = _
    "=IFERROR(IF(" & columnLetter1 + "7" & "/(" &  columnLetter1 + "5" & "*oee)"LESS THAN"VLOOKUP(" & columnLetter + "1" &  ",LineHours,4,FALSE)*'Look Up'!" & columnLetter2 + "5" & ","  & columnLetter1 + "7" & "/(" & columnLetter1 + "5" &  "*oee),VLOOKUP(" & columnLetter + "1" & ",LineHours,4,FALSE)*'Look  Up'!" & columnLetter2 + "5" & "),"") "

But it gives another error..

If you could correct the formula line with the correct "'s or &s or +s, that would be great.

Thanks.


[Edit]
Oh and "columnLetter1 + 7" equates to cell AG7.

 

[CharlesChuckieCharles]

& columnLetter2 + 5 & "),"""")" the end could do with 4 " in a row
why bother with columnLetter variables if its all fixed?

 

[Legacy 185734]

Thanks for the help! That worked Didn't realise an extra "" were needed on the end.

Ok now I'm getting problems with my next formula. Whats wrong with it?

ActiveCell.Formula = _
            "=IF(" & columnLetter1 + "4" & "=IFERROR(VLOOKUP(" & columnLetter + "1" & ",LineHours,4,FALSE)*'Look Up'!" & columnLetter2 + "5" & ",""),"",IFERROR((VLOOKUP(" & columnLetter + "1" & ",LineHours,4,FALSE)*'Look Up'!" & columnLetter2 + "5" & ") - " & columnLetter1 + "4" & ",VLOOKUP(" & columnLetter + "1" & ",LineHours,4,FALSE)*'Look Up'!" & columnLetter2 + "5" & "))"

Where would the extra ""'s need to be..cus I've tried adding them but it didn't fix it.

Once this formula is fixed, my document will be complete, so thank you very much again!

And they aren't fixed variables. I'm just showing you a snippet of the code.

 

[CharlesChuckieCharles]

errors are all quotes 's

Use the Immediate window to debug the line
simple

 

[Legacy 185734]

Yea I found where to add the extra """".

Thanks for the help, all working now .

 

[Ruddles]

Please note that when entering code, it kept ignoring everything after a "<", so I have replaced "<" with ""LESS THAN"". In the actual code there is a "<" (without quotations).

If you insert a single space after a < then the forum software works properly:-

=IF(A< 100,"Yes","No")

I know, it's annoying.