Updating cells
 

I have a workbook with merged cells with code that increases row height as
needed according to size of the string. I created a macro that essentially
goes to each such cell and hits <F2 <enter causing the cell to update and
readjust to the new string size. But since the macro uses Activecell.Formula,
inserting the formula, if I change the formula, I have to remember to change
it in the code, too.
Is there any way to accomplish updating the formula results without using
Activecell.Formula in VBA?
Thanks

--
Scafidel
Lafayette, Louisiana

Updating cells
 

Why do you need to remember to change it in the code?
Why keep the formula in the code at all?

Just capture the formula from the sheet:

Dim S as String
S = Range("A1").Formula

and then later on:

Range("A1").Formula = S


The alternative is to use SendKeys to refresh all formula cells:

Sub refreshh()
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
For Each rr In r
rr.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub

--
Gary''s Student - gsnu200855


"Scafidel" wrote:

I have a workbook with merged cells with code that increases row height as
needed according to size of the string. I created a macro that essentially
goes to each such cell and hits <F2 <enter causing the cell to update and
readjust to the new string size. But since the macro uses Activecell.Formula,
inserting the formula, if I change the formula, I have to remember to change
it in the code, too.
Is there any way to accomplish updating the formula results without using
Activecell.Formula in VBA?
Thanks

--
Scafidel
Lafayette, Louisiana

Updating cells
 

Thanks, Sendkeys was the ticket!
--
Scafidel
Lafayette, Louisiana


"Gary''s Student" wrote:

Why do you need to remember to change it in the code?
Why keep the formula in the code at all?

Just capture the formula from the sheet:

Dim S as String
S = Range("A1").Formula

and then later on:

Range("A1").Formula = S


The alternative is to use SendKeys to refresh all formula cells:

Sub refreshh()
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
For Each rr In r
rr.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub

--
Gary''s Student - gsnu200855


"Scafidel" wrote:

I have a workbook with merged cells with code that increases row height as
needed according to size of the string. I created a macro that essentially
goes to each such cell and hits <F2 <enter causing the cell to update and
readjust to the new string size. But since the macro uses Activecell.Formula,
inserting the formula, if I change the formula, I have to remember to change
it in the code, too.
Is there any way to accomplish updating the formula results without using
Activecell.Formula in VBA?
Thanks

--
Scafidel
Lafayette, Louisiana