Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving part of a column into another column | Excel Worksheet Functions | |||
Ron DeBruin Macro - Moving Sheet Name from Last Column to Column A | Excel Worksheet Functions | |||
moving a row to a column | Excel Worksheet Functions | |||
Why is my tab key moving my cursor from column A to column k? | Excel Discussion (Misc queries) | |||
Freezing the data in a column and moving the calculations to the next column | Excel Programming |