input box value into formula written with VBA

[jcgroove]

How can I use input box values in a formula that is put into a cell with VBA?


Example:

Sub comment_count()
Dim NUM1 As Variant
Dim NUM2 As Variant
NUM1 = InputBox("Enter the low range of comment numbers.", "ENTER")
NUM2 = InputBox("Enter the high range of coment numbers.", "ENTER")

Application.Worksheets("Install").Range("E17").FormulaArray = "=SUMPRODUCT(--ISNUMBER(MATCH(""*""&ROW(INDIRECT(""& NUM1:NUM2 &""))&""*"",$E$18:$E$2500&"""",0)))"

End Sub

 

[HarleyQuinn]

Looking at your code I'm not 100% sure what you want the formula to look like but try something like:

"=SUMPRODUCT(--ISNUMBER(MATCH(""*"" & ROW(INDIRECT(""" & NUM1 & ":" & NUM2 & """)) & ""*"",$E$18:$E$2500,0)))"

For future reference, further VBA queries would be better posted in forum707

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com