Formula in macro or in sheet/cells?

[Heine]

Hi,

I would like to put my lookup formula in the macro instead of in the
cells.

=IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"";(VLOOKUP(A9;raab1;3;FALSE))))


Any suggestion as to how this is done most easily? What are the
implications of this? Is it at all smart? The idea is that I can use
the macro in many sheets instead of copying the formulas from sheet to
sheet.

Thanks in advance!

/Heine

 

[Don Guillett]

modify this idea to suit
Sub makeformulae()
Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row)
With frng
.Formula = "=vlookup(a16,$a$16:$b$22,2,0)"
.Formula = .Value'changes to a value if desired
End With
End Sub

 

[Heine]

Thanks for the template, Don - what is frng?

/Heine

 

[Don Guillett]

just a name. Name it whatever you like such as Myrng or Crng or xxxxx

 

[Heine]

Hi again Don

I have problems understanding this line:

Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row)

what is "a"? - where do I fill in my data in this line exactly?

/Heine

 

[Don Guillett]

If you can't think this out it might be easier if you just tell me what your
range (rabbi) is.

 

[Heine]

thanks Don

My range is C1:E115 - but it could be changing over time.

/Heine

 

[Don Guillett]

try

Sub makeformulae()
Set frng = Range("f16:f" & Cells(Rows.Count, "C").End(xlUp).Row)
With frng
.Formula = "=vlookup(a9,raab1,3,0)"
'or
'.Formula = "=vlookup(a9,$c$1:$e$1000,3,0)"
.Formula = .Value'changes to a value if desired
End With
End Sub

 

[Heine]

thx Don,

I will be back in a few days to try it out. Thanks for your help so
far. And I hope you will stand by for further assistance.

/Heine

 

[Heine]

Hi again Don

My formula now looks exactly like this:

Sub makeformulae()
Sheets("Bogholderi").Select
Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row)
With frng
Formula =
"=if(a9="";"";if(isna(vlookup(a9;råb1;3;false));"";(vlookup(a9;råb1;3;false))))"
Formula = .Value 'changes to a value if desired
End With
End Sub

As I understand it this should set my defined formula active in all the
cells in column O from row 9 - that is it should return the result of
the formula in the entire column (almost) - but nothing happens. Any
thoughts?

And what can I use this line for?

Formula = .Value 'changes to a value if desired

/Heine

 

[Don Guillett]

when you have a with statement you must use the period before what pertains
to that with.
You apparently did not copy and modify mine. So, try putting a . before
formula
Formula
..Formula

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi again Don

My formula now looks exactly like this:

Sub makeformulae()
Sheets("Bogholderi").Select
Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row)
With frng
Formula =
"=if(a9="";"";if(isna(vlookup(a9;råb1;3;false));"";(vlookup(a9;råb1;3;false))))"
Formula = .Value 'changes to a value if desired
End With
End Sub

As I understand it this should set my defined formula active in all the
cells in column O from row 9 - that is it should return the result of
the formula in the entire column (almost) - but nothing happens. Any
thoughts?

And what can I use this line for?

Formula = .Value 'changes to a value if desired

/Heine