PDA

View Full Version : Get the reference from other sheet & go next row automatical



ooitzechyi
09-22-2016, 09:01 PM
Hi,
I'm trying to get the shape count by color by referring the color code in another sheet ("Summary") and input the result to that sheet ("Summary"), but it seems like not functioning and only show result in first row.
In Sheet1, I have shapes with different colors and either group or ungroup.
In Sheet('Summary'), I have my RGB code in Col C, Col D, Col E (Row 2 - 5).
I want the result to be input to Col F according to the code in Col C - E.
For eg,
My reference: (Col C - Col E)
Row 2: 255 200 255
Row 3: 0 255 0
Row 4: 200 200 200
Row 5: 0 0 255

My Result: (Col F)
Row 2: 2
Row 3: 11
Row 4: 0
Row 5: 5

Now what I could get was on Row 2 = 0, Row 3 - 5 = ""


Private Sub Ignore_Click()
Dim sh As Sheet1
Dim shp As Shape
Dim shp2 As Shapes
Dim shprange As ShapeRange

CountShape = 0
CountChildShape = 0

'Sheet1.Shapes.SelectAll
Set SmClr = Application.ThisWorkbook.Sheets("Summary")

LRow1 = SmClr.Range("C" & Rows.Count).End(xlUp).Row + 1
LRow2 = SmClr.Range("D" & Rows.Count).End(xlUp).Row + 1
LRow3 = SmClr.Range("E" & Rows.Count).End(xlUp).Row + 1
LRowC = SmClr.Range("C" & LRow1)
LRowD = SmClr.Range("D" & LRow2)
LRowE = SmClr.Range("E" & LRow3)

For Each shp In Sheet1.Shapes
If shp.Type = msoGroup Then
Set shprange = shp.Ungroup
Set oMyGroup = shprange.Group

If shprange.Fill.ForeColor.RGB = RGB(LRowC, LRowD, LRowE) Then
CountChildShape = CountChildShape + 1
End If

End If
Next shp

For Each shp In Sheet1.Shapes
If shp.Type <> msoGroup Then
If shp.Fill.ForeColor.RGB = RGB(LRowC, LRowD, LRowE) Then
CountShape = CountShape + 1
End If
End If
Next shp

CntShp = CountShape + CountChildShape

LastRow = SmClr.Range("F" & Rows.Count).End(xlUp).Row + 1
Application.ThisWorkbook.Sheets("Summary").Range("F" & LastRow).Value = CntShp

End Sub

Thank you.

mana
09-24-2016, 04:50 AM
I think you can't count shapes in your code.
These threads were not helpul for you?
Filter ShapeCount for Grouped Shape (http://www.vbaexpress.com/forum/showthread.php?57203-Filter-ShapeCount-for-Grouped-Shape&p=349457&highlight=#post349457)
How to count no of shapes in a group? (http://www.vbaexpress.com/forum/showthread.php?57150-How-to-count-no-of-shapes-in-a-group&p=349210&highlight=#post349210)

ooitzechyi
09-25-2016, 05:57 PM
Hi mana,
Those threads did help me but due to further request from user, those codings done need to further modify.
For eg, those threads were to count the shape with colors that I had defined and shows data in the cell defined, but according to user, they already have the shapes with colors (more than thousand shapes for different workbook, and they have no idea on how many shapes/colors they have). Thus, I need to modify to the way where the VB to auto check the color code and count the shapes with same color code.
There might have a better way then what I had tried as above thread but I have limited knowledge on this (my first task in VB), this is the only way I could think of.....

Anyway, I could get the above code done by changing the "Dim shp2 As Shapes" to "Dim shp2 as Shape'.
Thanks for your guides, else I think I could probably still stuck at somewhere.... :)