VBA sum in differentes sheets columns and rows

[Yaron Yehezkel]

Hey guys!
I have some problem with the sun function in the VBA, and for some reason i can't find the solution.

I have a data of employees and theirs monthly salary. At the end of each table, in each of the 5 specific sheets, is the "Montly Salary in DOLAR" column. I'm trying to figure out the VBA code to sum the "Montly Salary in DOLAR" column.
I need that the total amount of all employees monthly salary (the sum) will be placed under the column "Monthly Salary in DOLAR" and one cell after the last data line.

The problem is that in each sheet, where the "Monthly..." columns is, there are different number of rows and columns (because not all the employees worked all those 5 months).

Any help would be appreciated.

Thank you

 

[RayFrye]

This code will allow you to:
1. Walk the Worksheets in a Workbook
2. Walk the row & columns in a Worksheet looking for a Value
3. The Msgbox output the SheetName and Row and Column of the Search String

The GetExcelColumn converts a number to a column (i.e., Number 1 = Character A, Number 2 = Character B, etc.)
FindDataByLocation exits on the first fine of the search string.

Modify for your specific application. Enjoy.

Option Explicit
Sub start()
    Call WorksheetLoop("Monthly Salary in DOLLAR")
End Sub

Sub WorksheetLoop(LookFor)

    Dim WS_Count As Integer
    Dim I As Integer
   
    ' Set WS_Count equal to the number of worksheets in the active workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count
   
    ' Begin the loop.
    For I = 1 To WS_Count
   
        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
   
        MsgBox FindDataByLocation(ActiveWorkbook.Worksheets(I).Name, LookFor)
   
    Next I

End Sub


Public Function FindDataByLocation(WorksheetName, LookFor)

    Dim lastrow, lastColumn, RowLoop, ColumnLoop, sht

    Sheets(WorksheetName).Select
    lastrow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
   
    Set sht = ThisWorkbook.Worksheets(WorksheetName)
        sht.UsedRange
        lastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
   
    For RowLoop = 1 To lastrow
   
        For ColumnLoop = 1 To lastColumn
       
            If Range(GetExcelColumn(ColumnLoop) & RowLoop).Value = LookFor Then
                FindDataByLocation = WorksheetName & " " & GetExcelColumn(ColumnLoop) & RowLoop
                Exit Function
                        
            End If
        Next
   
    Next
  
End Function

Public Function GetExcelColumn(ByVal iCellNo As Integer) As String 'Ray

    Dim iBeg, iEnd As Integer

    ' If 1-26, then this is an easy conversion
    If iCellNo < 27 Then
        GetExcelColumn = Chr$(iCellNo + 64)
    Else
    ' Now we have to account for AA-ZZ
        iBeg = iCellNo \ 26     ' Get the first letter
        iEnd = iCellNo Mod 26   ' Get the second letter
        If iEnd = 0 Then
           ' If iEnd is 0, then it is Z, which should be 26
            iEnd = 26
        '** you need to subtract 1 from the initial letter otherwise your lettering will be the next letter in the alphabet

        iBeg = iBeg - 1
        End If
        GetExcelColumn = Chr$(iBeg + 64) & Chr$(iEnd + 64)
    End If
   
End Function

 

[Yaron Yehezkel]

I can't do it dynamically and automatically?
The thing is that i want to build a VBA code in one button (on the first sheet) that will calculate the monthly salary of my employees that spread over 5 sheets without the Msgbox output the sheetname and row and column of the search string.

Thanks!

 

[RayFrye]

I notice that you are new to this forum. Certainly, everything can be done dynamically and automatically. This forum is intended, I think, to provide users with examples of ways to address and solve a specific problem. The code I provide can be built on, the Msgbox gives you the "Worksheet Name" and the Location of "Monthly Salary in DOLLAR" text within that Worksheet. All you need to do is write the VBA code get the resultant value and drop it into a Worksheet. If your VBA skills are insufficient to expand on the code I provided then you really want someone to write the code for you; which I guess is fine. But you will have to ask questions once you have run the code. Your next question might be; "Now that I know the Worksheet name and Location of the search value within that worksheet, how do I get the value and drop it into a Worksheet". Or you might ask, how do I "link" this code to a button image I can "push". So, over time someone will help you write to code; but in baby steps suggesting you are trying to learn as we go. On the other hand if you want someone to write to code, I am sure someone here would be willing to do that. No idea how that works, as I believe this forum discourages trolling for free lance work.

 

[RayFrye]

Consulting Services This is a link to free lance work supported by Mr Excel.

 

[Yaron Yehezkel]

Thank you for the clarification!
I know how to use the VBA but this task was really complex for me so i needed some help!

Thanks anyway for your time.

 

[RayFrye]

I will help. Ask your next question.

 

[Yaron Yehezkel]

Appreciate that!

So, the thing is, I need to sum the last column in certain of tables in specific sheets.
The sheets named by : "employees 1", "employees 2"... and so on until 5 (must say that they are not the only sheets in the workbook),

As I explained, the problem is that in each sheet, where the "Monthly Salary in DOLLAR" columns is (which is the last column in the tables), there are different number of rows and columns (because not all the employees worked all those 5 months).

So basically, it will be great if you can send the me the code, i assume that after i will read the full code i will understand it.

Best regards!