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)?

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)?

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)?

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)?

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)?

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)?