View Full Version : Percentage covered by values using VBA
bbuk9999
05-20-2016, 07:18 AM
I am trying in the attached file to show the percentage in sheet (Yard Map) covered by the values in sheet (Container Yard). So when I remove one of the values in sheet (Container Yard) it does not show the percentage in the current file. For example: if I remove one of the values in sheet (Container Yard), in the related cell in sheet (Yard Map) should 80 appears. The attached file is a dynamic table with values generated in VBA. Thanks in advance.
mdmackillop
05-21-2016, 07:41 AM
Add this to the Container Yard worksheet module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iNumPerH As Long, iNumCol As Long
Dim i As Long, k As Long
iNumPerH = Application.CountA(Range("A:A")) - 1
iNumCol = Application.CountA(Range("1:1")) - 2
With Sheets("Yard Map")
For k = 1 To iNumPerH
For i = 1 To iNumCol
.Cells(1, 2 + i).Value = i
.Cells(1 + k, 2 + i).Value = Application.WorksheetFunction.CountA(Sheets(1).Cells(1 + k, 2 + i), Sheets(1).Cells(1 + k + iNumPerH, 2 + i), Sheets(1).Cells(1 + k + 2 * iNumPerH, 2 + i), Sheets(1).Cells(1 + k + 3 * iNumPerH, 2 + i), Sheets(1).Cells(1 + k + 4 * iNumPerH, 2 + i)) / 5 * 100
Next i
Next k
End With
End Sub
bbuk9999
05-22-2016, 10:32 AM
Thanks mdmackillop for the assistance, but it does not do the required when the number of the columns and rows are big for example 10 and 15. Can you make it works with big numbers as well. Thanks
mdmackillop
05-22-2016, 11:45 AM
Seems to work for me. Can you post your workbook with the larger data
bbuk9999
05-22-2016, 12:00 PM
When I remove one of the cell values with a big data in sheet (Container Yard), it does not show the expected percentage in sheet (Yard Map). Thanks
mdmackillop
05-22-2016, 12:39 PM
I'm using your code to recreate the Yard Map table. I only added the CountA lines to determine the dimensions. I'll try to find what the issue is.
mdmackillop
05-22-2016, 01:07 PM
Change this line
iNumPerH = Application.CountA(Range("A:A")) - 1
to
iNumPerH = Application.Max(Range("B:B"))
bbuk9999
05-22-2016, 05:06 PM
Thanks mdmackillop , it works.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.