Vba if loop help please

[kino4city]

Hi all,
I am useless on vba and excel but together OMG!!!.

Well, I want it to search my range(E12:E39), and find out if it contains "APPLE", then if so i need it to insert chart, name "APPLE CHART", in cell b2, size(Height,460pix/Length,720pix), but if cell b2 is active then 2 rows lower...

If you can help that would be great, if you need more details just let me know.

Thanks in advance,
Chris

 

[g4cox]

Hi,

what sort of chart are you after + where is the data to go in the chart?

Thanks

 

[kino4city]

Hi,

what sort of chart are you after + where is the data to go in the chart?

Thanks

Just a basic bar chart, and it based on a different sheet called APPLE GREEN,
but i just want it to show a view only copy in new worksheet in the cell b2 and then...

the loop needs to go round the range an identify which what info is there, i have used APPLE as an example,but for intanse if i have APPLE entered twice i don't want it to show to Charts for APPLE if that makes sense or even helps??

 

[g4cox]

Try this I think this is just about what you are after? note I have selected data from Sheet1 G10:G20 so change as you need.

Sub Applechart()

    Dim r, f As Range
    Set r = Range("E12", "E39")
    Set f = r.Find(What:="Apple", LookIn:=xlValues, LookAt:=xlPart)

        If Not (f Is Nothing) Then
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=Range("'Sheet1'!$G$10:$G$20")
        ActiveChart.ChartType = xlColumnClustered
        Selection.Width = 720
        Selection.Height = 460
        Selection.Left = 49
        Selection.Top = 13
        ActiveChart.SetElement (msoElementChartTitleAboveChart)
        ActiveChart.ChartTitle.Text = "APPLE CHART"

    Else
    MsgBox ("No Apple")
    End If

End Sub