CODEWITHSUNDEEP
excelvbaexcel-2016
ZKT
ZKT

Reputation: 1

Updating Workday.intl formula with vba userform value

I have a workday formula that calculates a date based on a month duration. I would like the user to set that duration in the userform and update the formula to that. I am receiving a Run-time error: '1004' and unsure why.

Formula in cell F525: =WORKDAY.INTL(WORKDAY(F3,(26*22)),1,"0111111")

Update to formula: =WORKDAY.INTL(WORKDAY(F3,(10*22)),1,"0111111")

Private Sub Calculate_CommandButton_Click()
    Sheets("Project Plan").Select
    Application.ScreenUpdating = False

    Dim lookupValue As String

    Dim targetCell As Range


    ' Get the value from the UserForm control

    lookupValue = Me.GoLiveDuration_TextBox.Value


    ' Set the target cell where the VLOOKUP formula is

    Set targetCell = Range("F525") ' Replace "A1" with the actual cell containing the formula


    ' Update the VLOOKUP formula with the new lookup value

    targetCell.Formula = "=WORKDAY.INTL(WORKDAY(F3,(" & lookupValue & " *22)),1, & Chr(34) 0111111 &   Chr(34) &) "
 

    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 41

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

This:

targetCell.Formula = "=WORKDAY.INTL(WORKDAY(F3,(" & _
     lookupValue & " *22)),1, & Chr(34) 0111111 &   Chr(34) &) "

Should be

targetCell.Formula = "=WORKDAY.INTL(WORKDAY(F3,(" & _
     lookupValue & " *22)),1,""0111111"") "

You can escape quotes by doubling them up

Better to qualify any Range references with a worksheet:

Private Sub Calculate_CommandButton_Click()
    Dim lookupValue As String

    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("Project Plan")
        .Select
        
        lookupValue = Me.GoLiveDuration_TextBox.Value
        .Range("F525").Formula = "=WORKDAY.INTL(WORKDAY(F3,(" & _
              lookupValue & " *22)),1,""0111111"")"
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions