MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Getting a formula to copy from inside VBA

  Asked By: Harry    Date: Nov 04    Category: MS Office    Views: 2539

When a user inputs a new row using my userform, I want to make it such that
the old values from the previous row are copied into the new row (basically
a copy/paste function). However, if the user has entered a formula such as
"=text(B115,"ddd")", I would like it to act EXACTLY like they did a select
and drag down operation, and therefore update the cell to be
"=text(B116,"ddd"). I have used the "record macro" function to find out
what Excel would do... and updated the references to be variable based
(instead of static).

Here's the rub.

If I run the resulting code from a module (while only calling it with
variables declared and set someplace else in the module) it works great.
However, if I call it from my userform, it will evaluate the formula and
place the result in the correct place, but will not paste the formula. I've
even transferred the .Formula property of the cell being copied into a
String (used a MSGBOX to verify that it was copied) and tried to transfer
that String into the .Formula property of the new
cell... no go. (It evaluated the formula, put the result in the box)

Here's the code I'm using with the .Formula property

If MakingNew = True and LineNo > 5 Then
cells(LineNo, i).Formula = cells(Lineno-1,i).Formula
end if

Anyone have any ideas?



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Getting a formula to copy from inside VBA Or get search suggestion and latest updates.