How do I convert a Formula into Code???
 

Hi there,

I have a problem, I have various formulas that I use within my worksheet,
however when I update the sheet where the formulas reference I get #REF
errors all the time. This really isn't a problem as I go into the formulas
and change them so that they are okay. However I would really like to
convert the formulas to code, so that when I run a macro the formulas are
inserted into a specified cell.
The formulas are as follow =IF(COUNTIF('1103 Working Sheet Last
Week'!A:A,'1103 Working Sheet'!A2)=0,'1103 Working Sheet'!A2,"") which I
would like to go into cell B3 and the following formula I would like to go
into cell D1 =VLOOKUP(B1,'1103 Working Sheet'!$A$2:$B$1500,2,FALSE) using
code.

regards Harry

How do I convert a Formula into Code???
 

Option Explicit
Sub testme()
With ActiveSheet
.Range("b3").Formula _
= "=IF(COUNTIF('1103 Working Sheet Last Week'!A:A," _
& "'1103 Working Sheet'!A2)=0,'1103 Working Sheet'!A2,"""")"
.Range("D1").Formula _
= "=VLOOKUP(B1,'1103 Working Sheet'!$A$2:$B$1500,2,FALSE)"
End With
End Sub

Lot's of times you can record a macro when you do the work and you'll get the
code you need.



BigH wrote:

Hi there,

I have a problem, I have various formulas that I use within my worksheet,
however when I update the sheet where the formulas reference I get #REF
errors all the time. This really isn't a problem as I go into the formulas
and change them so that they are okay. However I would really like to
convert the formulas to code, so that when I run a macro the formulas are
inserted into a specified cell.
The formulas are as follow =IF(COUNTIF('1103 Working Sheet Last
Week'!A:A,'1103 Working Sheet'!A2)=0,'1103 Working Sheet'!A2,"") which I
would like to go into cell B3 and the following formula I would like to go
into cell D1 =VLOOKUP(B1,'1103 Working Sheet'!$A$2:$B$1500,2,FALSE) using
code.

regards Harry

--

Dave Peterson