PDA

View Full Version : [SOLVED] Looping through shapes by Name VBA Excel



emax79
12-15-2013, 07:35 AM
Everyone …… I have a excel thathas Different Shapes with unique Names I gave them …Example .Jimmy1, Jimmy 2 ,etc . I can use the “loop” below to get all theproperties of the shapes enclosed in my worksheet . What I would liketo do is “Loop through the workbook shapes “ By there “name”not just there category “shape” for instance . I need to do thisbecause I need to perform separate calculations on each shape Bythere “unique “ Name . The code below works fine . I just wouldlike some advice on how to loop through them by there “name” Iassigned to them .Then after i get there name . Run "different Calculation Loop :mkay on each shape By "identifying its name . Thank you guys . Happy Computing




Sub GetShapeProperties()
Dim sShapes As Shape, lLoop As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet1")
' Addheadings for our lists. Expandas needed
ws1.Range("A1:C1") = _
Array("Shape Name ","Height", "Width")
' Loop through all shapes onactivesheet
For Each sShapes In ws1.Shapes
If sShapes.Type <>msoFormControl Then
' IncrementVariable lLoop forrow numbers
lLoop = lLoop + 1
With sShapes
' Add shape properties
ws1.Cells(lLoop +1, 1) = .Name
ws1.Cells(lLoop +1, 2) = .Height
ws1.Cells(lLoop +1, 3) = .Width
' Follow the same patternfor more
End With
End If
Next sShapes

End Sub




Shape Names


Jimmy 1
Jimmy 2
Jimmy 3

Kenneth Hobs
12-15-2013, 10:20 AM
Loop and check the Name property for the shapes and use and IF to compare to the Left(aShape.Name, 6) = "Jimmy " or such.

emax79
12-15-2013, 10:33 AM
Thank you for your help.... Hey Kennth i think a found another way to so it but i still am in need of assistance . Because i knwo your good ! lol .... I have this code to loop through them after on the actual spread sheet . But i do not know how to create the "vba " function for this . Another man suggested a nested if staement but "its not good . Its to many arguments for one function ion the actual worksheet . I 'll enclose the code below. Thank you so much Kenneth


I have a excel Where i Have 6 different names as variables . Thevaribles are dynamic . They end up in different rows everytime i dothis function . Can someone please help me on how to go about Loopingthrough these names . Finding out which name is which . thenperforming a different calculation depending on what the "name "is . The names always end up in Column A . They just end up indifferent rows . So i would like to know how to do a function /macrothat looks like this ....... If name = Anna d1=b1*c1 . If name =Harry d1 = b1+c1 ....... Theres are seven different names with a different function for each





Name

Number

Number







Anna

36.476833

7.96618557

if name = Anna Number "times Number"





Eric

17.451147

5.80342102

if name = Eric Number + Number





Ronald

21.125067

6.45224857

If name = Ronald Number "divided"By Number



Roxanne

12.071469

2.55927372








Harry

0

30.1705704







Vallerie

11.67783

0







Wally

0

6.81271219









Roxanne to Wally are blan .But if you can imagine they have similar functions I ll use "if name = Roxane Number + Number" Just for structuring purposes thank you Mr Kenneth

emax79
12-15-2013, 10:35 AM
shoot .....this the information apologies






Name

Number

Number







Anna

36.476833

7.96618557

if name = Anna Number "times Number"





Eric

17.451147

5.80342102

if name = Eric Number = Number





Ronald

21.125067

6.45224857

If name = Ronald Number "divided"By Number



Roxanne

12.071469

2.55927372







Harry

0

30.1705704







Vallerie

11.67783

0







Wally

0

6.81271219

emax79
12-15-2013, 10:40 AM
Name

Number

Number







Anna

36.476833

7.96618557

if name = Anna Number "times Number"





Eric

17.451147

5.80342102

if name = Eric Number = Number





Ronald

21.125067

6.45224857

If name = Ronald Number "divided"By Number



Roxanne

12.071469

2.55927372







Harry

0

30.1705704







Vallerie

11.67783

0







Wally

0

6.81271219







sorry what a mess ... It looks like this

mancubus
12-15-2013, 11:05 AM
hi.

you may attach a sample file...

Kenneth Hobs
12-15-2013, 11:07 AM
Try posting a simple workbook to help us help you better and more easily.

emax79
12-15-2013, 11:32 AM
I have 7 different names that i would like to perform different calculations based on there name . I cannot do this with a nested if statment for there are to many "arguments " excel says . So i am looking for a suggestion to do a "loop" possibley . The nuts and bolts of the function is ..... Find name in Row Column A ......... Compile a number into column D ...... Based On The Name of the Person ...... I have attached a Workbook named "7 Name " Thank you guys so much . Apologies for the Incorrect posting ...............

Kenneth Hobs
12-15-2013, 03:14 PM
I am not sure how shapes relates to this post.

In a Module paste the code below. In D2 enter the formula, =NameFormula(A2) and fill down.


Option Explicit

' =NameFormula(A2)
Function NameFormula(nRange As Range) As Variant
Dim s As String, op As String

Application.Volatile False
s = Split(nRange.Value2)(0)

Select Case True
Case s = "Aric" Or s = "Wally" Or s = "Eric"
op = "+"
Case s = "Ronny"
op = "-"
Case s = "Rick"
op = "/"
Case s = "Vallerie"
op = "="
Case s = "Harry"
op = "*"
Case Else
s = ""
End Select

If s = "" Then
NameFormula = Evaluate("=NA()")
Exit Function
End If

s = "" & "=(" & nRange.Offset(0, 1).Value & _
op & nRange.Offset(0, 2).Value & ")"
NameFormula = Evaluate(s)
End Function

snb
12-15-2013, 03:44 PM
I didn't know 'their' is being written as 'there' in Michigan.
You might take into account that not all members of this forum use the same vernacular you do. So try to use the most common English spelling possible.

emax79
12-15-2013, 03:49 PM
I didn't know 'their' is being written as 'there' in Michigan.
You might take into account that not all members of this forum use the same vernacular you do. So try to use the most common English spelling possible.

emax79
12-15-2013, 05:56 PM
I wonder how one Learns this stuff ? Amazing stuff to someone like me just Learning . Much Gratitude Kenneth :yes

snb
12-16-2013, 02:13 AM
I think I'd prefer:


Function NameFormula(nRange As Range) As Variant
Select Case Split(nRange)(0)
Case "Aric", "Wally", "Eric"
NameFormula = nRange.Offset(, 1) + nRange.Offset(, 2)
Case "Ronny"
NameFormula = nRange.Offset(, 1) - nRange.Offset(, 2)
Case "Rick"
NameFormula = nRange.Offset(, 1) / nRange.Offset(, 2)
Case "Vallerie"
NameFormula = nRange.Offset(, 1) = nRange.Offset(, 2)
Case "Harry"
NameFormula = nRange.Offset(, 1) * nRange.Offset(, 2)
End Select
End Function