Need help on moving to next column
 

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

Need help on moving to next column
 

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

Need help on moving to next column
 

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

Need help on moving to next column
 

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

Need help on moving to next column
 

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

Need help on moving to next column
 

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

Need help on moving to next column
 

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

Need help on moving to next column
 

Tom,

Up front let me state that I truly appreciate ALL of the help!!!

I read your response late last night...and I have to admit this is
begining to push my understanding and knowledge of VBA.
I found some other posts about declaring variables in the way you
mentioned and have added sections of the code to mine and it seems to
work. It opens each M*wkbk and runs the calculations, moves to the next
column and does it for the next M*wkbk in the folder. WORKS GREAT!!!

I have two requests; One would you look at the code below and see if it
is in the most efficient way?

And, I plan to use this same basic code to work on additional projects
and the sumproduct there is a little more in depth than this one, based
on your solution before; would I do something like this: If you think
it best to repost as a new post for this I will...

=IF(INDEX(Mkt!F:F,MATCH(Summary!C42,MKT!$C:$C,0))= """",IF(ISERROR(SUMPRODUCT((s"&iwkbk&"htm!$A$1:$A$ 10000=Summary!C42....etc...for
EACH section?

Below is the original formula

=IF(INDEX(Mkt!F:F,MATCH(Summary!C42,Mkt!$C:$C,0))= "",IF(ISERROR(SUMPRODUCT((SP2.htm!$A$1:$A$10000=Su mmary!C42)*(SP2.htm!$E$1:$E$10000="yes")*(SP2.htm! $A$1:$A$10000=Summary!C42)*(SP2.htm!$D$1:$D$10000= "Is
this project 100% complete to
specifications?"))/SUMPRODUCT((SP2.htm!$A$1:$A$10000=Summary!C42)*(SP 2.htm!$D$1:$D$10000="Is
this project 100% complete to
specifications?"))),"",SUMPRODUCT((SP2.htm!$A$1:$A $10000=Summary!C42)*(SP2.htm!$E$1:$E$10000="yes")* (SP2.htm!$A$1:$A$10000=Summary!C42)*(SP2.htm!$D$1: $D$10000="Is
this project 100% complete to
specifications?"))/SUMPRODUCT((SP2.htm!$A$1:$A$10000=Summary!C42)*(SP 2.htm!$D$1:$D$10000="Is
this project 100% complete to specifications?"))),"Inactive")

Here is the entire code, at this point:


Sub MAPREPORT()
Dim lCount As Long
Dim wbResults As Workbook, wsFiles As Worksheet
Dim wbCodeBook As Workbook
Dim iWkbk As Variant



Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
.FileName = "m*.htm"

If .Execute 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults =
Workbooks.Open(FileName:=.FoundFiles(lCount), UpdateLinks:=0)

'Run Calculations
Windows("MAPReport.xls").Activate
Dim LastRow As Long
Dim lastcol As Long
iWkbk = iWkbk + 1
Set wbResults = Workbooks.Open(wsFiles.Range("M" &
iWkbk).Value).Worksheets(1)
With Worksheets("MAP")
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 = "=SUMPRODUCT((m" & iWkbk &
".htm!$A$1:$A$10000=$C2)*(m" & iWkbk & ".htm!$E$1:$E$10000=""Yes""))"



.Value = .Value
End With
End With



wbResults.Close SaveChanges:=True

Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub











Tom Ogilvy wrote:
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)?