No, that is completely different.
Now it sounds like you want to address a different file each time you run
the macro. You will have to figure out how you want to pass a variable in
to specify the sequence number, but the general approach would be
Formula =
"=SUMPRODUCT((m" & i & ".htm!$A$1:$A$10000=$C2)*(m" & i &
".htm!$E$1:$E$10000=""Yes""))"
Where the variable "i" holds the sequence number.
--
Regards,
Tom Ogilvy
wrote in message
ups.com...
Tom,
The file name will advance sequentially in each column and not a
row...I tried it and it will not work correctly. Do I change row to
column??
Thanks
Hans
Tom Ogilvy wrote:
You can try something along the lines of:
Formula =
"=SUMPRODUCT((Indirect(""m""&row(A1)&"".htm!$A$1:$ A$10000"")=$C2)*(Indirect(""m""
&row(A1) &"".htm!$E$1:$E$10000"")=""Yes""))"
assuming the number in the file name will advance sequentially in each
row.
--
Regards,
Tom Ogilvy
" wrote:
Tom,
I think I spoke too soon...the last code is fine, but I was not
thinking this through...
Where I have .formula="=1+3" the actual formaula I have been using
is:
..Formula =
"=SUMPRODUCT((m1.htm!$A$1:$A$10000=$C2)*(m1.htm!$E $1:$E$10000=""Yes""))"
Not a problem until I decided to change the way I do this and since I
am calling each .htm wkbk as M*.htm utilizing the wildcard...well I am
sure you know where the problem is now.
I cannot use a wildcard in sumproduct, i.e. sumproduct((m*.htm etc)
*(m*.htm etc))... what do I do know that I have screwed this up!?!
I have found references to the equivilant of sumproduct in vba but not
what I am trying to do.
Thanks,
Hans
wrote:
Tom,
Thanks alot works like a charm!!
Hans
Tom Ogilvy wrote:
Windows("MAPReport.xls").Activate
Dim LastRow As Long
Dim lastcol as Long
With Worksheets("Summary")
Lastcol = .cells(2,columns.count).End(xltoLeft)(1,2).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range(.Cells(2,lastcol),.Cells(LastRow,lastcol))
.Formula = "=1+3"
.Value = .Value
End With
End With
--
Regards,
Tom Ogilvy
" wrote:
I use this line of code to look for the data that I need:
Windows("MAPReport.xls").Activate
Dim LastRowA As Long
With Worksheets("Summary")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("F2:F" & LastRow)
.Formula = "=1+3"
.Value = .Value
End With
End With
But, how do I state that if F2:F is not empty go to the next
available
column (in this case column G)?