To divide value in each cell to the sum of the corresponding columns.

Hi! I am a beginner to VBA, and would really appreaciate help.

I need to divide value in each cell to the sum of the corresponding column, and there are several columns.
Vakye in each cell in column K should be divided to the sum of column K, and the resutls will be written in in other sheet's column. And this operation is to be done to columns from K to V.
I found how to calculate sum for the K:V columns, but do not know how now to divide each cell values to the sum of the column. My script now looks like this, which does not work:


For iCol = 11 To 22 'Columns K:V

iSummory = .SUM(Range(Cells(2, iCol), Cells(LastRow, iCol)))

For Each C In Range(Cells(2, iCol), Cells(LastRow, iCol))
Worksheets("Sheet1").Cells(2, 1) = C / iSummory
Next C

Next iCol

Thank you!

Please Login or Register  to view this content.

Hi! Thank you SixthSense and AlphaFrog for the promt response!

AlphaFrog the script works! Thanks! Now I need to make it more dynamical withe more variables, since I have dozens of spreadsheets with such K:V column, which lenght is different in each spreadsheet. I will try by myself now and if I fail, then I will have to come back to this post and as SixthSense suggest to attach the file.

Thank you again

Hi! It is me again. And I am back due to my failure of automating the calculations that I wanted.
I have attached the file.
As I wrote I need to divide each cell from column K to V to the sum of each column (calculate the percentage) in the sheet “Main” and write down the result in columns from B to M in the next sheet “Result” of the workbook:

In "Result" sheet in "Gramake_NO_NW" workbook:
B C D
='MAIN!'K2/'MAIN!'K770 ='MAIN!'L2/'MAIN!'L770 ='MAIN!'M2/'MAIN!'M770 So on
='MAIN!'K3/'MAIN!'K770 ='MAIN!'L3/'MAIN!'L770 ='MAIN!'M3/'MAIN!'M770
='MAIN!'K3/'MAIN!'K770 ='MAIN!'L3/'MAIN!'L770 ='MAIN!'M3/'MAIN!'M770
='MAIN!'K4/'MAIN!'K770 ='MAIN!'L4/'MAIN!'L770 ='MAIN!'M4/'MAIN!'M770
='MAIN!'K5/'MAIN!'K770 ='MAIN!'L5/'MAIN!'L770 ='MAIN!'M5/'MAIN!'M770
So on So on So on

The solution of AlphaFrog is working for only one workbook, and I found at that for some reasons it calculates percentages wrongly, i.e. twice less of the value.
I have dozen of these files that need to be calculated this way and each file has the same amount of columns (i.e. from K to V), but the length of these columns differ, i.e. the number of rows is different. So I need something with loops and dynamic variables. I tried to make the range of columns and rows to be array, and as a range, but I can’t. Please help!

So here the script with columns and rows as an array that looks like with the error message of “Subscript out of range” - 1st script.
Or when the columns and rows as a range, then I receive “Type mismatch” and “Overflow” - 2nd script

1)

PHP Code: 

Private Sub SUMkystArray()

   'Raw Data
   Dim Raw_Data_Array() As Variant ' Raw_Data_Array (iRaw, iCol)
   Dim LastRow As Long: LastRow = [a65000].End(xlUp).Row
   Dim StartRow As Integer:  StartRow = 2
   Dim iRow As Long
   Dim k As Integer 'each raw in Raw_Data_Array
   
   Dim iCol As Integer
   Dim ColStart As Integer:  ColStart = 11
   Dim i As Integer ' each col in Raw_Data_Array
   
   'Result Table
   Dim jRow As Integer: jRow = 2 ' result raw
   Dim jCol As Integer: jCol = 3 ' result column
 

With Application.WorksheetFunction

For i = 0 To 10 'Columns K:V
    iCol = ColStart + i  'from column K to V in  Raw_Data_Array
    
    For k = 0 To 20000 'Rows from to 20 000  rows
      iRaw = StartRow + k
        Worksheets("Sheet3").Cells(jRow, jCol + i) = Raw_Data_Array(k, i) / .SUM(Raw_Data_Array(iRaw, iCol))
    Next k
    jRow = jRow + 1 ' next row
Next i
End with 
End Sub 


or
2)

PHP Code: 

Private SUMkystRange()

   Dim LastRow       As Long
   Dim iRow          As Long
   Dim jRow          As Integer: jRow = 2 ' result row
   Dim iCol          As Integer
   Dim jCol          As Integer ' result column
   
   LastRow = 0
   
   Dim C As Variant ' each cell value in Range K:V in the "MAIN" sheet
   Dim i As Integer
   
   'Find last row
   For iCol = 11 To 22 'Columns K:V
       iRow = Cells(65536, iCol).End(xlUp).Row
       If iRow > LastRow Then LastRow = iRow
   Next iCol
   
   With Application.WorksheetFunction
    
    'Place column totals in row after current last row
      For iCol = 11 To 22  'Columns K:V in "Main" sheet
         
         For jCol = 2 To 13 ' Columns B to M in "Result" sheet

            For i = 0 To 10000
            
            C = C + i
            
            For Each C In Range(Cells(2, iCol), Cells(LastRow, iCol)) ' Range in "MAIN" sheet
                If C.Value >= 0 Then
                Worksheets("Result").Cells(jRow, jCol) = CInt(C) / .SUM(Range(Cells(2, iCol), Cells(LastRow, iCol)))
                End If
            Next C
            
            Next i
            
            jRow = jRow + 1
                        
        Next jCol
         
      Next iCol
    
    End With
    
    End Sub 


Originally Posted by DanaGaraf

The solution of AlphaFrog is working for only one workbook, and I found at that for some reasons it calculates percentages wrongly, i.e. twice less of the value.
I have dozen of these files that need to be calculated this way and each file has the same amount of columns (i.e. from K to V), but the length of these columns differ, i.e. the number of rows is different. So I need something with loops and dynamic variables. I tried to make the range of columns and rows to be array, and as a range, but I can’t. Please help!

The original code I provided sums the values from the entire column
.Formula = "='Sheet2'!K2/SUM('Sheet2'!K:K)"
If there is a column "Total" at the bottom of the data, the result from macro formula will be "twice less" as it were because it's summing the entire column including the "Total".

• Can you better describe the nature of your data and any variance from workbook to workbook?
• Do all your data columns have Totals at the bottom or some do and some don't? Is the column Total always from a =SUM formula
• Do you always want to start at column K and go to the last used column?

Originally Posted by AlphaFrog

The original code I provided sums the values from the entire column
.Formula = "='Sheet2'!K2/SUM('Sheet2'!K:K)"
If there is a column "Total" at the bottom of the data, the result from macro formula will be "twice less" as it were because it's summing the entire column including the "Total".

• Can you better describe the nature of your data and any variance from workbook to workbook?
• Do all your data columns have Totals at the bottom or some do and some don't? Is the column Total always from a =SUM formula
• Do you always want to start at column K and go to the last used column?

HI! Thank you for your reply!

Yes, sorry, let me to describe the task more clear:

---I have dozen of workbooks containing data in their first sheet from column A to column AA. But I need only data from column K to column V to be converted to the percentage. That is the value of each cell need to be divided to the total value of all cells in the corresponding column. The length of each columns K:V, i.e. the number of rows is different from one workbook to next workbook.

---No, the total of each column is not to be necessary to be written at the end of each column and the same with formula, it is not to be necessary to be =SUM formula. However I do not know other way of calculated the percentage of each cell in the corresponding column.

---Yes, I need the data from fixed column K to column V.

And I need the results to be written at the next sheet of each corresponding workbook from column B to M. Or, well, in the perfect world I would like to have the results to be written in a new workbook's first sheet from column B to M and then saved as tab-tebilimited text file named with the same name as the initial raw workbook (with columns A:AA, the data needed from K to V).

But at least, at this stage I would like to have the percentage calculations to be automated with more dynamic variables, with no specific name of sheets, workbooks and etc.

Thank you again for your consideration !