VBA Apply formula but put Value only in Cell

[senthil murugn]

Hi,

Creating a task list, where user feeds the task name, start date and no of days required(through form).
By using WorkDay.INTL end date is calculated and it is working fine, but when this is
copied to excel cell it goes with formula too. This is fine when new excel sheet is confined
to local system. But when email this to some other person formula cell becomes #REF error
due to holiday table is available only in source system.

Code is as follows

>>> use code - tags <<<

    Cells(Cell_No, 2).Value = Str1 & Str2
    Cells(Cell_No, 3).Value = TaskTextBox.Value
    Cells(Cell_No, 4).Value = AssignedToComboBox.Value
    Cells(Cell_No, 5).Value = CDate(StartDateTextBox.Value)
    Cells(Cell_No, 6).Value = DaysTextBox.Value
****************************************************************
  ************ Following lines needs to be fixed ****************

    Cells(Cell_No, 7).Value = CDate(WorksheetFunction.WorkDay_Intl(Startdate, Days, 11, Range("Table4")))  ' goes with formula but needs only value
    Cells(Cell_No, 7).Copy
    Cells(Cell_No, 7).PasteSpecial xlPasteValues

************** End of Fixation Line ************************
**************************************************************
    Cells(Cell_No, 8).Value = DevStatusComboBox.Value
    Cells(Cell_No, 9).Value = DeliverableTextBox.Value
    Cells(Cell_No, 10).Value = VerifyByComboBox.Value
    Cells(Cell_No, 11).Value = VerifStaComboBox.Value

Need help in transfer of only the value and not the formula to excel. Tried in web but mostly its meant for row copy & paste.

Thanks & Regards
Senthil

 

[Hui]

Startdate and days are named formula
They will need to be defined on the other pc
also Table 4 must have the same name
Also the usercontrols have custom names, so check those exist

 

[senthil murugn]

Startdate and days are named formula
They will need to be defined on the other pc
also Table 4 must have the same name
Also the usercontrols have custom names, so check those exist

Hi,

Project lead assigns start date (from form) and no of days(from form) through his system. Then VBA creates the
excel sheets for those entries then email the new task list to the respective assigned persons.

So they no need to have those start date and Data table in their system. Only at excel generation side
needs to have the required data. At excel generation system it works fine since all required data are present.

What is required is, let VBA use formula to calculate, then returns only vales to excel cell and not the formula to excel.

Hope this might help to understand the situation.

Thanks & regards
senthil

 

[senthil murugn]

Hi,

Fixed the error, reason is not technical its a mistake in copy/paste in sheets.
Please close this thread.