VBA sum in differentes sheets columns and rows

Yaron Yehezkel

New Member
Joined
Jul 11, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
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.


VBA Code:
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
 
Last edited by a moderator:

Yaron Yehezkel

New Member
Joined
Jul 11, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • PIC.png
    PIC.png
    29.9 KB · Views: 7

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
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

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019

ADVERTISEMENT

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

Yaron Yehezkel

New Member
Joined
Jul 11, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.
 

Yaron Yehezkel

New Member
Joined
Jul 11, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,446
Messages
5,642,201
Members
417,259
Latest member
gtacw

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