CODEWITHSUNDEEP
excelvba
Goku
Goku

Reputation: 89

How to ammend the excel formula in vba code?

I have two formulas The original formulas in Excel sheet are:

  1. =YEAR(G2)*100+WEEKNUM(G2;21)
  2. =TEXT(G2;"JJJJ-MM")

In VBA they becomes:

  1. = "=YEAR(RC[-58])*100+WEEKNUM(RC[-58],21)"
  2. = "=TEXT(RC[-59],""JJJJ-MM"")"

G2 is a second cell from column C_Date

The following code creates a new column Due CW then try to find column C_Date and selects a second cell in it. I am trying to ammend the above VBA formulas in this code but facing syntax errors.

It would be great if someone puts me on the right track!

Sub Due CW()
    Dim r1 As Range, r2 As Range, r3 As Range
    Dim emptyColumn As Long, LastRow As Long
    
    'find empty Column (actually cell in Row 1)
    emptyColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    If emptyColumn > 1 Then
        emptyColumn = emptyColumn + 1
    End If
    
    Cells(1, emptyColumn).Value = "Due KW"
    
    With Rows(1)
        Set r1 = .Find(What:="C_Date", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
        Set r3 = .Find(What:="Due CW", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not r1 Is Nothing And Not r2 Is Nothing And Not r3 Is Nothing Then
            LastRow = Cells(Rows.Count, r1.Column).End(xlUp).Row
            r3.Offset(1, 0).Resize(LastRow - 1).Formula = "=" & "YEAR" &(r1.offset(1,0).Address(0, 0))&"*"&"100"&"+"&"WEEKNUM"&(r1.offset(1,0).Address(0, 0),21)"
                                                           
        End If
    End With
End Sub

Upvotes: 1

Views: 55

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You have an issue with the quotes in the very end of the following line and the parenthesis should be within the quotes too:

r3.Offset(1, 0).Resize(LastRow - 1).Formula = "=" & "YEAR(" & r1.Offset(1, 0).Address(0, 0) & ")*" & "100" & "+" & "WEEKNUM(" & r1.Offset(1, 0).Address(0, 0) & ",21)"

you can easily test it like

Debug.Print "=" & "YEAR(" & r1.Offset(1, 0).Address(0, 0) & ")*" & "100" & "+" & "WEEKNUM(" & r1.Offset(1, 0).Address(0, 0) & ",21)"

to verify the result, this should now resolve to:

=YEAR(ADDRESS)*100+WEEKNUM(ADDRESS),21)

You can even write this shorter for better readability by concatenating the strings directly like:

r3.Offset(1, 0).Resize(LastRow - 1).Formula = "=YEAR(" & r1.Offset(1, 0).Address(0, 0) & ")*100+WEEKNUM(" & r1.Offset(1, 0).Address(0, 0) & ",21)"

Upvotes: 1

Related Questions