• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Apply formula but put Value only in Cell

senthil murugn

New Member
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 <<<
Code:
    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
 
Last edited by a moderator:

Hui

Excel Ninja
Staff member
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

New Member
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
 
Top