Need help on moving to next column

[hansjhamm]

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

 

[Guest]

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

 

[hansjhamm]

Tom,

Thanks alot works like a charm!!

Hans

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

 

[hansjhamm]

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

 

[Guest]

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.

 

[hansjhamm]

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]

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.

 

[hansjhamm]

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=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)*(SP2.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)*(SP2.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