How to put a macro variable in a formula

  • Thread starter Thread starter Kristof
  • Start date Start date
K

Kristof

Hi,

How can I put a variable from a macro in a formula

I have the following VBA code

Dim Lijst As Range
y = (ActiveWorkbook.Sheets.Count - 5) * 3
Set Lijst = Worksheets("Gemiddelden+reproduceerbaarheid").Range("a3:h" & 3 +
y)
Resultaat = "=Application.WorksheetFunction.DGet(Lijst, 3,
Range("R100:S101"))"
Range("r3").Formula = "=IF(S5="""",""""," & Resultaat & ")"

It gives the following error message for the last line:
Run-time error '1004':
Application-definedor obect-defined error.


The result for Resultaat is good but apparently it is a problem with
combining the formula.
I know that you can use the If.. then .. else Statement but this formula is
the beginning of a much more complex formula.

Many Thanks for your help.
 
I think DGET is part of the Analysis Toolpak, so you need to set a reference
to that in the VBE, and then use

Resultaat = DGet(Lijst, 3, Range("R100:S101"))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I think I goofed there, so maybe it is just

Resultaat = Application.DGet(Lijst, 3, Range("R100:S101"))

code ran througfh okay, but not sure of the validity of the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top