0

I'm trying to add code to enter a formula to several columns and copy to the end of the row. The VBA works for one of the columns (AB) but I get a syntax error for column AC. The formula works in excel if I enter it manually. Any idea why this is and how it can be fixed?

    Sub AddHeaders()

    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim used As Range
    Set used = ws.UsedRange

Dim lastRow As Integer
    lastRow = used.Row + used.Rows.Count - 1

ws.Range("AB6:AB" & lastRow).Formula = "=IF(ISNUMBER(J6),J6,IF(LEN(J6)=9,DATE(RIGHT(J6,4),LEFT(J6,1),MID(J6,3,2)),IF(LEN(J6)=10,DATE(RIGHT(J6,4),LEFT(J6,2),MID(J6,4,2)))))"
ws.Range("AC6:AC" & lastRow).Formula = "=IF(AND(WORKDAY(TODAY(),20)-AB6<=28,WORKDAY(TODAY(),20)-AB6>=0),"yes","no")"

End Sub

Thanks

FuriousD
  • 119
  • 9
  • 1
    Also see how to correctly [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), and why you should [use Long, not Integer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Dec 07 '21 at 15:00
  • 2
    Please, try `"=IF(AND(WORKDAY(TODAY(),20)-AB6<=28,WORKDAY(TODAY(),20)-AB6>=0),""yes"",""no"")"` for the second formula. Doubling the double quotes... – FaneDuru Dec 07 '21 at 15:01
  • @FaneDuru This worked. I assume this is always needed for absolute values then? – FuriousD Dec 07 '21 at 15:06
  • @BigBen - useful stuff, I'll take time to read and amend as required. Thanks for the references. – FuriousD Dec 07 '21 at 15:07
  • In order to check if a (VBA) formula is correct, please use `Debug.Print` for the string you intend using. If it does look as you want (in formula), you keep it. If not, adjust it to look as needed. And yes, any **necessary** double quote (in formula) must be doubled in the string used in VBA. I do not understand what you mean by "absolute values"... You must build a string able to be accepted by the formula. In order to write "test", you should use ""test"". Try it in `Immediate Window` (`Debug.Print`), please. – FaneDuru Dec 07 '21 at 15:13

0 Answers0