Please help..... VBA Code amendment

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Hi All

I am considering of arranging my code so the output is different. The code I have at the minute (Below) is extracting data from multiple spreadsheet copying to summary sheet.

Code is:

Code:
Then 'If there is entry in col F to L  or N to S copy the row to Summary
     
               lRowTo = lRowTo + 1 'last used row in the summary +1
                                        With wsSumm ' the copy
                                            .Rows(lRowTo).Value = WS.Rows(R).Value
                                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                                            
                                        End With
                                     Else   
                                   End If
                              Else
                               
                            End If
                        Next R
                         LogEntry ExcelFile:=Filenm, _
                                 Week:="Week " & sWeeks(iWeekPtr), _
                                 Message:="PROCESSED"
                    End If
                Else
                    lRowTo = lRowTo + 1
                    With wsSumm
                        .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                        .Cells(lRowTo, "B").Value = "NOT FOUND"
                    End With
                   LogEntry ExcelFile:=Filenm, _
                            Week:="Week " & sWeeks(iWeekPtr), _
                            Message:="NOT FOUND"
                End If
            Next iWeekPtr
        
                           .DisplayAlerts = False
                ActiveWorkbook.Close
                .DisplayAlerts = True
            End With
        End If
    End If
Next I
    
 
With Application
    .StatusBar = False
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

The current layout is:

Team Name
Row which has been copied

Total:

Team Name
week number, Row which has been copied

Total:

I was wondering what I would have to do to edit the code so it would look like this:

Team Name,week number, Row which has been copied

This would be really really helpful, and I would really appreciate anybody help...

Thanks in advance.

Andrew
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Where you see ".Cells(lRowTo, "A").Value "
Change it to:

myAnswer = myAnswer & ", " & and the rest of that line.


Then the last time you see ".Cells(lRowTo, "A").Value"

Change the part after the =
to:
.Cells(lRowTo, "A").Value = myAnswer & ", " & and the rest of that line.

This should work?
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Joe Was

Thanks very much for your reply... really appreciate this.

I have entered your solution and I get a compile error - Variable not defined. Any ideas?

There is more to this code, but I have only put the bit I thought needed changing:

Full code:

Code:
Option Explicit
Dim wsLog As Worksheet
Sub ListInfobyFile()
Dim sWeeks() As String, sList As String
Dim iWeekPtr As Integer, iPtr As Integer
Dim iWkCur As Integer, iWkLow As Integer, iWkHigh As Integer
Dim wsSumm As Worksheet, WS As Worksheet, wsPWD As Worksheet
Dim Folderpath As String, Filenm As String
Dim I As Long, R As Long, C As Long, lRowTo As Long, lRowEnd As Long
Dim lRowStart As Long, lRowCU As Long, lErrNum As Long
Dim sPassword As String
Dim V As Variant, ChWeek As Variant, vFileList As Variant
Dim Bcol As Range
Dim firstAddress As String
Set wsSumm = ThisWorkbook.Sheets("Summary") ' Should be defined as ThisWorkbook
Set wsLog = ThisWorkbook.Worksheets("Activity Log") ' Should be defined as ThisWorkbook
Set wsPWD = ThisWorkbook.Sheets("Passwords") ' Should be defined as ThisWorkbook


'Look in this file path to get a list of files in the folder, change this as required
Folderpath = ThisWorkbook.Path
Filenm = Dir(Folderpath & "\*.xls", vbNormal + vbReadOnly)

vFileList = GetFileList(Folderpath & "/*.xls")

If IsArray(vFileList) = False Then
    MsgBox "No Excel files found in " & Folderpath & vbCrLf & _
            "Macro abandoned."
    Exit Sub
End If

ChWeek = Application.InputBox(prompt:="Enter Week(s) required separated by comma" & vbCrLf & _
                                    "(e.g. 1,2,3,4)..." & vbCrLf & _
                                      "... or 'Cancel' to exit.", _
                              Type:=2)

If ChWeek = False Then Exit Sub

sWeeks = Split(ChWeek, ",")
iWkLow = 999
For iWeekPtr = LBound(sWeeks) To UBound(sWeeks)
    iWkCur = Val(sWeeks(iWeekPtr))
    If iWkCur < 1 Or iWkCur > 52 Then
        MsgBox "Invalid Week number entered"
        Exit Sub
    End If
    If iWkCur < iWkLow Then iWkLow = iWkCur
    If iWkCur > iWkHigh Then iWkHigh = iWkCur
Next iWeekPtr

With wsSumm
    lRowTo = .UsedRange.Row + .UsedRange.Rows.Count - 1
    If lRowTo > 2 Then .Rows("5:" & lRowTo).ClearContents
    lRowTo = .Cells(Rows.Count, "B").End(xlUp).Row + 1
End With

With Application
    .ScreenUpdating = False
    'Ensure macros dont fire when opening w/books
    .EnableEvents = False
End With

For I = LBound(vFileList) To UBound(vFileList)
    Filenm = vFileList(I)

    If ThisWorkbook.Name <> Filenm Then
        
        'Paste the name
        lRowTo = lRowTo + 3
        wsSumm.Cells(lRowTo, "A").Value = Filenm
        
        lRowStart = lRowTo + 1
        
        'open File
        V = "*"
        On Error Resume Next
        V = WorksheetFunction.Match(Filenm, wsPWD.Columns("A"), 0)
        On Error GoTo 0
        If IsNumeric(V) Then
            sPassword = wsPWD.Cells(V, "B").Text
        Else
            sPassword = ""
        End If
        On Error Resume Next
        Workbooks.Open FileName:=Folderpath & "\" & Filenm, _
                        ReadOnly:=True, _
                        Password:=sPassword
        lErrNum = Err.Number
        On Error GoTo 0
        If lErrNum > 0 Then
            LogEntry ExcelFile:=Filenm, _
                     Week:="******", _
                     Message:="CANNOT OPEN"
        Else
            For iWeekPtr = LBound(sWeeks) To UBound(sWeeks)
                Set WS = Nothing
                On Error Resume Next
                Set WS = Sheets(sWeeks(iWeekPtr))
                On Error GoTo 0
                If Not WS Is Nothing Then
                    If WS.Tab.ColorIndex = xlColorIndexNone Then
                        lRowTo = lRowTo + 1
                        With wsSumm
                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                            .Cells(lRowTo, "B").Value = "NOT UPDATED"
                        End With
                        LogEntry ExcelFile:=Filenm, _
                                 Week:="Week " & sWeeks(iWeekPtr), _
                                 Message:="NOT UPDATED"
                    Else
                        Application.StatusBar = "Processing " & Filenm & ": Week " & _
                                                sWeeks(iWeekPtr)
                        
                        'Check Range
                        'Get last row to check
                        'lRowEnd = WS.Range("B" & Rows.Count).End(xlUp)' --->>> Removed not working due to
     ' This last row detect was not really being detected correctly  as there was some other data down row 222 in col B workbook AEA
     
    '------------------Find the which row total is in in the data source------
    ' Searches for Total…in Col B and set LrowEnd to one row less
    'NOTE - becareful not put more than one "TOTAL" or "total" or "Total" _
     or other combination in Col B of you sheets you get the data from.
                                With WS.Range("B12:B65336")
                                       Set Bcol = .Find(LCase("TOTAL"), LookIn:=xlValues)
                                       If Not Bcol Is Nothing Then
                                           firstAddress = Bcol.Address
                                Do
                                lRowEnd = Bcol.Row - 1 ' set one row less than cell with "TOTAL"
                              
                                Set Bcol = .FindNext(Bcol)
                                Loop While Not Bcol Is Nothing And Bcol.Address <> firstAddress
                                End If
                                End With

                       '===========Start of looking for the entries to copy ==========
                       
                        For R = 12 To lRowEnd ' For  Rows of 12 to last
                            If LCase$(WS.Cells(R, "B").Text) <> "Total" Then ' if the entry in Row B is not = Total
                               ' For C = 6 To 7 'Cols F:L ' ===> Removed DOING NOTHING
                                 
                                   If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) Or _
                                        Application.CountA(WS.Range(WS.Cells(R, 14), WS.Cells(R, 15))) _
                                        Then 'If there is entry in col F to L  or N to S copy the row to Summary
     
                                        lRowTo = lRowTo + 1 'last used row in the summary +1
                                        With wsSumm ' the copy
                                            .Rows(lRowTo).Value = WS.Rows(R).Value
                                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                                            
                                        End With
                                     Else   'Exit For '===> Removed DOING NOTHING
                                    End If
                              Else
                               'Next C '===> Removed DOING NOTHING
                            End If
                        Next R
                         LogEntry ExcelFile:=Filenm, _
                                 Week:="Week " & sWeeks(iWeekPtr), _
                                 Message:="PROCESSED"
                    End If
                Else
                    lRowTo = lRowTo + 1
                    With wsSumm
                        .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                        .Cells(lRowTo, "B").Value = "NOT FOUND"
                    End With
                   LogEntry ExcelFile:=Filenm, _
                            Week:="Week " & sWeeks(iWeekPtr), _
                            Message:="NOT FOUND"
                End If
            Next iWeekPtr
        
            lRowTo = lRowTo + 2
            wsSumm.Cells(lRowTo, "B").Value = "TOTAL"
            For iPtr = 1 To 7
                wsSumm.Cells(lRowTo, iPtr + 5).FormulaR1C1 = "=sum(R" & lRowStart & "C:R[-1]C)"
            Next iPtr
            wsSumm.Cells(lRowTo, "M").FormulaR1C1 = "=sum(R" & lRowStart & "C:R[-1]C)"
            With Application
                .DisplayAlerts = False
                ActiveWorkbook.Close
                .DisplayAlerts = True
            End With
        End If
    End If
Next I
    
lRowTo = lRowTo + 2
wsSumm.Cells(lRowTo, "B").Value = "GRAND TOTAL"
For iPtr = 1 To 7
    wsSumm.Cells(lRowTo, iPtr + 5).FormulaR1C1 = "=sum(R4C:R[-1]C)/2"
Next iPtr
wsSumm.Cells(lRowTo, "M").FormulaR1C1 = "=sum(R4C:R[-1]C)/2"
  
With Application
    .StatusBar = False
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

Function GetFileList(FileSpec As String) As Variant
'  Courtesy John Walkenbach
'   Returns an array of filenames that match FileSpec
'   If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String
    
    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
    
'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

'   Error handler
NoFilesFound:
    GetFileList = False
End Function

Sub LogEntry(ByVal ExcelFile As String, _
             ByVal Week As String, _
             ByVal Message As String)
Dim lRow As Long
Dim vData(1 To 4) As Variant

lRow = wsLog.Cells(Rows.Count, "A").End(xlUp).Row + 1
vData(1) = Format(Now(), "dd-mmm-yy hh:mm:ss")
vData(2) = ExcelFile
vData(3) = Week
vData(4) = Message
wsLog.Range("A" & lRow & ":D" & lRow).Value = vData
End Sub

Thanks again..
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

At the top of your code you have "Option Explicit" this statement tells Excel VBA not to define Variables on the fly, only hard-coded Variable Defines will be excepted as valid. So with all the other "Dim" statements you must add:

Dim myAnswer$
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Just tried this, and its coming up with an error, end of statement expected???
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452

ADVERTISEMENT

Any other ideas???
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You have posted two sets of code?
The first set is not the right portion after seeing the other part you posted.
Your not a programmed are you?

What are you doing, what do you have and what do you want to end up with?

Also if this is not all of the code for this Sub, post all of it. Also describe the data that is being worked with by this code, down to the data cells locations!
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Im not a programmer, but learning VBA in my own time..

Ok... Here is my current code:

Code:
Option Explicit 
Dim wsLog As Worksheet 
Sub ListInfobyFile() 
Dim sWeeks() As String, sList As String 
Dim iWeekPtr As Integer, iPtr As Integer 
Dim iWkCur As Integer, iWkLow As Integer, iWkHigh As Integer 
Dim wsSumm As Worksheet, WS As Worksheet, wsPWD As Worksheet 
Dim Folderpath As String, Filenm As String 
Dim I As Long, R As Long, C As Long, lRowTo As Long, lRowEnd As Long 
Dim lRowStart As Long, lRowCU As Long, lErrNum As Long 
Dim sPassword As String 
Dim V As Variant, ChWeek As Variant, vFileList As Variant 
Dim Bcol As Range 
Dim firstAddress As String 
Set wsSumm = ThisWorkbook.Sheets("Summary") ' Should be defined as ThisWorkbook 
Set wsLog = ThisWorkbook.Worksheets("Activity Log") ' Should be defined as ThisWorkbook 
Set wsPWD = ThisWorkbook.Sheets("Passwords") ' Should be defined as ThisWorkbook 


'Look in this file path to get a list of files in the folder, change this as required 
Folderpath = ThisWorkbook.Path 
Filenm = Dir(Folderpath & "\*.xls", vbNormal + vbReadOnly) 

vFileList = GetFileList(Folderpath & "/*.xls") 

If IsArray(vFileList) = False Then 
    MsgBox "No Excel files found in " & Folderpath & vbCrLf & _ 
            "Macro abandoned." 
    Exit Sub 
End If 

ChWeek = Application.InputBox(prompt:="Enter Week(s) required separated by comma" & vbCrLf & _ 
                                    "(e.g. 1,2,3,4)..." & vbCrLf & _ 
                                      "... or 'Cancel' to exit.", _ 
                              Type:=2) 

If ChWeek = False Then Exit Sub 

sWeeks = Split(ChWeek, ",") 
iWkLow = 999 
For iWeekPtr = LBound(sWeeks) To UBound(sWeeks) 
    iWkCur = Val(sWeeks(iWeekPtr)) 
    If iWkCur< 1 Or iWkCur > 52 Then 
        MsgBox "Invalid Week number entered" 
        Exit Sub 
    End If 
    If iWkCur< iWkLow Then iWkLow = iWkCur 
    If iWkCur > iWkHigh Then iWkHigh = iWkCur 
Next iWeekPtr 

With wsSumm 
    lRowTo = .UsedRange.Row + .UsedRange.Rows.Count - 1 
    If lRowTo > 2 Then .Rows("5:" & lRowTo).ClearContents 
    lRowTo = .Cells(Rows.Count, "B").End(xlUp).Row + 1 
End With 

With Application 
    .ScreenUpdating = False 
    'Ensure macros dont fire when opening w/books 
    .EnableEvents = False 
End With 

For I = LBound(vFileList) To UBound(vFileList) 
    Filenm = vFileList(I) 

    If ThisWorkbook.Name<> Filenm Then 
        
        'Paste the name 
        lRowTo = lRowTo + 3 
        wsSumm.Cells(lRowTo, "A").Value = Filenm 
        
        lRowStart = lRowTo + 1 
        
        'open File 
        V = "*" 
        On Error Resume Next 
        V = WorksheetFunction.Match(Filenm, wsPWD.Columns("A"), 0) 
        On Error GoTo 0 
        If IsNumeric(V) Then 
            sPassword = wsPWD.Cells(V, "B").Text 
        Else 
            sPassword = "" 
        End If 
        On Error Resume Next 
        Workbooks.Open FileName:=Folderpath & "\" & Filenm, _ 
                        ReadOnly:=True, _ 
                        Password:=sPassword 
        lErrNum = Err.Number 
        On Error GoTo 0 
        If lErrNum > 0 Then 
            LogEntry ExcelFile:=Filenm, _ 
                     Week:="******", _ 
                     Message:="CANNOT OPEN" 
        Else 
            For iWeekPtr = LBound(sWeeks) To UBound(sWeeks) 
                Set WS = Nothing 
                On Error Resume Next 
                Set WS = Sheets(sWeeks(iWeekPtr)) 
                On Error GoTo 0 
                If Not WS Is Nothing Then 
                    If WS.Tab.ColorIndex = xlColorIndexNone Then 
                        lRowTo = lRowTo + 1 
                        With wsSumm 
                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr) 
                            .Cells(lRowTo, "B").Value = "NOT UPDATED" 
                        End With 
                        LogEntry ExcelFile:=Filenm, _ 
                                 Week:="Week " & sWeeks(iWeekPtr), _ 
                                 Message:="NOT UPDATED" 
                    Else 
                        Application.StatusBar = "Processing " & Filenm & ": Week " & _ 
                                                sWeeks(iWeekPtr) 
                        
                        'Check Range 
                        'Get last row to check 
                        'lRowEnd = WS.Range("B" & Rows.Count).End(xlUp)' --->>> Removed not working due to 
     ' This last row detect was not really being detected correctly  as there was some other data down row 222 in col B workbook AEA 
      
    '------------------Find the which row total is in in the data source------ 
    ' Searches for Total…in Col B and set LrowEnd to one row less 
    'NOTE - becareful not put more than one "TOTAL" or "total" or "Total" _ 
     or other combination in Col B of you sheets you get the data from. 
                                With WS.Range("B12:B65336") 
                                       Set Bcol = .Find(LCase("TOTAL"), LookIn:=xlValues) 
                                       If Not Bcol Is Nothing Then 
                                           firstAddress = Bcol.Address 
                                Do 
                                lRowEnd = Bcol.Row - 1 ' set one row less than cell with "TOTAL" 
                              
                                Set Bcol = .FindNext(Bcol) 
                                Loop While Not Bcol Is Nothing And Bcol.Address<> firstAddress 
                                End If 
                                End With 

                       '===========Start of looking for the entries to copy ========== 
                        
                        For R = 12 To lRowEnd ' For  Rows of 12 to last 
                            If LCase$(WS.Cells(R, "B").Text)<> "Total" Then ' if the entry in Row B is not = Total 
                               ' For C = 6 To 7 'Cols F:L ' ===> Removed DOING NOTHING 
                                  
                                   If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) Or _ 
                                        Application.CountA(WS.Range(WS.Cells(R, 14), WS.Cells(R, 15))) _ 
                                        Then 'If there is entry in col F to L  or N to S copy the row to Summary 
      
                                        lRowTo = lRowTo + 1 'last used row in the summary +1 
                                        With wsSumm ' the copy 
                                            .Rows(lRowTo).Value = WS.Rows(R).Value 
                                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr) 
                                            
                                        End With 
                                     Else   'Exit For '===> Removed DOING NOTHING 
                                    End If 
                              Else 
                               'Next C '===> Removed DOING NOTHING 
                            End If 
                        Next R 
                         LogEntry ExcelFile:=Filenm, _ 
                                 Week:="Week " & sWeeks(iWeekPtr), _ 
                                 Message:="PROCESSED" 
                    End If 
                Else 
                    lRowTo = lRowTo + 1 
                    With wsSumm 
                        .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr) 
                        .Cells(lRowTo, "B").Value = "NOT FOUND" 
                    End With 
                   LogEntry ExcelFile:=Filenm, _ 
                            Week:="Week " & sWeeks(iWeekPtr), _ 
                            Message:="NOT FOUND" 
                End If 
            Next iWeekPtr 
        
            lRowTo = lRowTo + 2 
            wsSumm.Cells(lRowTo, "B").Value = "TOTAL" 
            For iPtr = 1 To 7 
                wsSumm.Cells(lRowTo, iPtr + 5).FormulaR1C1 = "=sum(R" & lRowStart & "C:R[-1]C)" 
            Next iPtr 
            wsSumm.Cells(lRowTo, "M").FormulaR1C1 = "=sum(R" & lRowStart & "C:R[-1]C)" 
            With Application 
                .DisplayAlerts = False 
                ActiveWorkbook.Close 
                .DisplayAlerts = True 
            End With 
        End If 
    End If 
Next I 
    
lRowTo = lRowTo + 2 
wsSumm.Cells(lRowTo, "B").Value = "GRAND TOTAL" 
For iPtr = 1 To 7 
    wsSumm.Cells(lRowTo, iPtr + 5).FormulaR1C1 = "=sum(R4C:R[-1]C)/2" 
Next iPtr 
wsSumm.Cells(lRowTo, "M").FormulaR1C1 = "=sum(R4C:R[-1]C)/2" 
  
With Application 
    .StatusBar = False 
    .EnableEvents = True 
    .ScreenUpdating = True 
End With 
End Sub 

Function GetFileList(FileSpec As String) As Variant 
'  Courtesy John Walkenbach 
'   Returns an array of filenames that match FileSpec 
'   If no matching files are found, it returns False 

    Dim FileArray() As Variant 
    Dim FileCount As Integer 
    Dim FileName As String 
    
    On Error GoTo NoFilesFound 

    FileCount = 0 
    FileName = Dir(FileSpec) 
    If FileName = "" Then GoTo NoFilesFound 
    
'   Loop until no more matching files are found 
    Do While FileName<> "" 
        FileCount = FileCount + 1 
        ReDim Preserve FileArray(1 To FileCount) 
        FileArray(FileCount) = FileName 
        FileName = Dir() 
    Loop 
    GetFileList = FileArray 
    Exit Function 

'   Error handler 
NoFilesFound: 
    GetFileList = False 
End Function 

Sub LogEntry(ByVal ExcelFile As String, _ 
             ByVal Week As String, _ 
             ByVal Message As String) 
Dim lRow As Long 
Dim vData(1 To 4) As Variant 

lRow = wsLog.Cells(Rows.Count, "A").End(xlUp).Row + 1 
vData(1) = Format(Now(), "dd-mmm-yy hh:mm:ss") 
vData(2) = ExcelFile 
vData(3) = Week 
vData(4) = Message 
wsLog.Range("A" & lRow & ":D" & lRow).Value = vData 
End Sub

The above code outputs the following:
ZTeamSummary.xls
ABCDEFGHIJKLM
4AEA-AtkinsonGroveCFB.xls
5Week23MoniqueSerranoM12926.56.578.585.069444
6
7TOTAL06.56.5078.585.069444
8
9Trialattendanceandsicknessreturn.xls
10Week23AkinyandeWalem12345677770
11Week23HortSydneym123456smpsmpsmpsmpsmp0
12
13TOTAL07777000
14
15GRANDTOTAL013.513.57148.585.069444
Summary


The output I would like is:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=15><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - ZTeamSummary.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=15><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=15><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value==SUM(G$5:G6) selected>G7<OPTION value==SUM(H$5:H6)>H7<OPTION value==SUM(I$5:I6)>I7<OPTION value==SUM(J$5:J6)>J7<OPTION value==SUM(K$5:K6)>K7<OPTION value==SUM(L$5:L6)>L7<OPTION value==SUM(M$5:M6)>M7<OPTION value==SUM(N$5:N6)>N7<OPTION value==SUM(G$10:G12)>G13<OPTION value==SUM(H$10:H12)>H13<OPTION value==SUM(I$10:I12)>I13<OPTION value==SUM(J$10:J12)>J13<OPTION value==SUM(K$10:K12)>K13<OPTION value==SUM(L$10:L12)>L13<OPTION value==SUM(M$10:M12)>M13<OPTION value==SUM(N$10:N12)>N13<OPTION value==SUM(G$4:G14)/2>G15<OPTION value==SUM(H$4:H14)/2>H15<OPTION value==SUM(I$4:I14)/2>I15<OPTION value==SUM(J$4:J14)/2>J15<OPTION value==SUM(K$4:K14)/2>K15<OPTION value==SUM(L$4:L14)/2>L15<OPTION value==SUM(M$4:M14)/2>M15<OPTION value==SUM(N$4:N14)/2>N15</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==SUM(G$5:G6) name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>J</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>K</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>L</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>M</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>N</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #fffff
 

Forum statistics

Threads
1,136,586
Messages
5,676,666
Members
419,639
Latest member
ShaunAldridge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top