BINGO!!!!!!!!!!!!!!!!!!!!!!
the angels are singing choruses now. Well, at least I am.

Thank you.
Now.
Perhaps it's part of the coding-- that's what it appears to me-- but for the
"first cell" and the "Second cell" I've noticed that they are "absolutely"
referenced.
I.e., $A$row, and $C$row
Is there a way that I can just get it to be $Arow, and $Crow?
I.e., I don't want the row to be absolutely referenced.
I'll be dragging the contents down to the end of the data set on my
principal worksheet.



"Dave Peterson" wrote:

That seems like a reasonable approach.

But change the actual assignment to:

NuA.formula = ....

SteveDB1 wrote:

Hi Dave,
I've taken this sub routine, and have been trying it.
I realized in my initial use that it placed the equation in cell A1.
Upon seeing that, I added the following, and while it doesn't call an error,
it doesn't place the equation in the cell of my choosing. In fact, it appears
to do nothing.

Dim NuA As Range
Set NuA = Nothing
On Error Resume Next
Set NuA = Application.InputBox(Prompt:="enter cell where to place this_
equation", Type:=8).Cells(1)

On Error GoTo 0
If NuA Is Nothing Then
Exit Sub 'user hit cancel
End If

The problem that I now appear to be having is that I want to choose the
placement of the equation-- which I thought the above would perform. It
however does not.
I've then changed the

ActiveSheet.range().formula

from having the "A1" in the parenthesis, to placing the NuA in there. This
does not work either.
I've made various choices of where to place this, and what it states/calls.
I removed the ActiveSheet to now just have the Range(NuA).fomula and that
does not work either.

So, my question then becomes-- what do I use to choose the placement of the
formula?

Thank you.

"Dave Peterson" wrote:

If the ranges are all on the same rows, but different (known) columns, you can
ask for the first range and then use that to get the other two ranges.

But if the cells to check are always in the same relative location, you could do
the same kind of thing--ask once and figure out the second cell's location.

I think you wrote that the ranges are always in the same relative position (for
the 3 argument version of your =sumproduct() function.

The other code that I suggested just asked the user to select a range. Build a
small test macro in a test workbook and try it out. You'll see how
application.inputbox is different from inputbox.

Anyway, this may get you to the next step:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myRngE As Range
Dim myRngF As Range
Dim myRngC As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim myFormula As String

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select the first criteria range",
_
Type:=8).Areas(1).Columns(1)
On Error GoTo 0
If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Set myCell1 = Nothing
On Error Resume Next
Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _
Type:=8).Cells(1)
On Error GoTo 0
If myCell1 Is Nothing Then
Exit Sub 'user hit cancel
End If

Set myCell2 = Nothing
On Error Resume Next
Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _
Type:=8).Cells(1)
On Error GoTo 0
If myCell2 Is Nothing Then
Exit Sub 'user hit cancel
End If

'start in column A and go over 2 columns to get myRngC
'and 2 more from C to E
'and 3 from C to F
Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2)
Set myRngE = myRngC.Offset(0, 2)
Set myRngF = myRngC.Offset(0, 3)

'shooting for:
'=sumproduct(
' (APN!$E$5:$E$200&""=$A14&"")
' *(APN!$F$5:$F$200=$C14)
' *(APN!$C$5:$C$200))

myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _
& "=" & myCell1.Address(external:=True) & "&"""")" _
& "*(" & myRngF.Address(external:=True) _
& "=" & myCell2.Address(external:=True) & ")" _
& "*(" & myRngC.Address(external:=True) & "))"

Activesheet.range("a1").Formula = myFormula

End Sub


SteveDB1 wrote:

Dave,
First, this is my first "full-scale" macro that I'm doing from scratch.
I've made various attempts in the past, but found that I understood far too
little to go where I wanted. As a result, I'd record actions that I wanted,
and then looked at the code to streamline what I could, because I'd do
actions in a somewhat disorderly manner. I've done about a couple of dozen or
so macros in that manner. Last week I started putting this macro together
based on some ideas I had and what I could understand from the Excel VBA 2007
Programmer's Reference manual from WROX.

I don't understand what myRngPer is.

Is it a variable inside your code (and what does it contain) or is it a range
name that you created in excel (Insert|name|define)?

MyRngPer is the name of the variable that I gave to select a range of data.
I did the following to code that.

dim MyRngPer as Range
MyRngPer = inputbox(Prompt:=".....",Title:=".......")

As for the large number of input boxes, I'd rather do it with a user's form.
However, my last attempt at a user form is still waiting for me to go back
and find out why it doesn't work the way I thought it would. I know it's
something I did wrong, I just haven't gone back to it yet to find out what I
did wrong. I had an interesting idea that had too many input boxes, like
this one does, so I wanted to try user forms.

Picking a range of data would be nice, automatically, if the workbooks we
have weren't so different in start, and end points.
As I said we have around 780 workbooks that we update regularly, and while
I've gone through about 50 to 100 with a manually entered version of what
I've shown you here, I'm tired of spending 5 minutes on each one, and wanted
to speed up the input process.


I don't understand why you're using &"" in your formula. Do you have a mixture
of cells that contain digits--but some are text and some are really numbers?


your statement on the &"" is correct. For reasons unknown to me, and work
done prior to my coming to work here, the columns where the &"" are used to
compare were given various data type formats. I tried using the sumproduct
equation without them for close to 4 months when I started finding one
situation where it would work great, and another where it wouldn't-- as you
can imagine it got really irritating fast. I wanted to help streamline the
process for other coworkers as well, and so it had to work under all
circumstance, regardless. Harley Grove, and another guy from Britain helped
me understand the benefit, and necessity of the &"". I think I've only found
one situation where it didn't work (out of thousands of lines, and perhaps a
100 workbooks; this includes linking different workbooks together with it),
and I was able to do something else that fixed it.

Instead of a series of inputboxes to ask for row numbers (and having to validate
all the possible errors), you could use application.inputbox and prompt the user
for a range--just point and select.


I just found the application.inputbox example on page 69 of the WROX
reference book. I'll use that instead of all the individual input boxes.
Thanks.

'and do you a prompt for the cell that contains the criteria for column F
comparison?

As to the last question, the
....(APN!$F$5:$F$200 = $C14)
where $C14 is what the range on the APN worksheet is looking for.
So, yes, it'd be helpful to have a prompt to call it.

Then the next question becomes: Is the data always in columns E, F and C?

Yes, for one use of the sumproduct.
I'll have a second use where only the third column changes. But then it'll
always be column B.
So, on my primary use, Columns E, F, and C
on my secondary use, Columns E, F, and B.
Both of these uses are standard.

Once in a great while-- I think there are 5 to 10 workbooks out of the
780--that will have two columns of the data normally only in column C.

I do remember one workbook that will have 4 columns of the data normally in
column B.
So, for these few occurences where there is something that differs, I can do
them manually, if I haven't done them already. Two of the odd ones I was
using sumif because I wasn't aware of the sumproduct at the time. I know that
all of that is still in one file. In fact, now that I think about it, it was
that file that got me looking more on how to speed things up. I literally was
testing one line at a time to see if I had everything.

Please talk to me more about what you're doing here below.


Dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select the first criteria range", _
type:=8)
on error goto 0
if myrng is nothing then
exit sub 'user hit cancel
end if

--

Dave Peterson


--

Dave Peterson