For Each inside another loop works only once after excel startup

shokarta

New Member
Joined
Nov 20, 2017
Messages
2
Hello guys,

I have an macro I am fighting last 5 days with no luck.

I am doing FOR loop every row filled and hidding shapes (pictures) based on value in Column C in every row.
So far I am only in stage that I wish to FOR loop of all shapes and hide or show (just to test that everything works fine so far).

The issue is, that the macro works fine the first time I run it, and if I run it again, I got an error... When I close excel and open again, then I can run the macro once only :/.

Code:
Sub ds()

    Dim lastrow As Long
    lastrow = Worksheets("Task_List").Cells(Rows.Count, 1).End(xlUp).Row


    Dim ShapeAction As Shape


    Dim i As Integer
    For i = 6 To lastrow


        For Each ShapeAction In Worksheets("Task_List").Shapes
            If ShapeAction.TopLeftCell.Row = i And ShapeAction.TopLeftCell.Column = 14 Then
                MsgBox ShapeAction.TopLeftCell.Row & "-" & ShapeAction.TopLeftCell.Column
            End If
            If ShapeAction.TopLeftCell.Row = i And ShapeAction.TopLeftCell.Column = 15 Then
                MsgBox ShapeAction.TopLeftCell.Row & "-" & ShapeAction.TopLeftCell.Column
            End If


        Next ShapeAction
    Next i


End Sub
In these past 5 days I have tried everything I could posibly find out on the internet, the different kind of IF conditions, such for example If Not Intersect(Range(Cells(i, 14), ActionShape.TopLeftCell) Is Nothing Then (and so many other ways to find shape in a cell.

Getting always same error on IF statement.

See images as errors

 

shokarta

New Member
Joined
Nov 20, 2017
Messages
2
Hello Guys,

I am lot of frustrated with this... I dont understand the behaviour.

Sometimes I isert new row and it works, another time it doesnt work.
Then Sometimes helps if I save the excel first before run the macro.

It is like something is keeping in cache, I tried to:
Set ShapeAction = Notning before Next ShapeAction but it didnt help...

Sometimes when the sheet changed (random cell which was before empty is filled now) works, sometimes not :/
 

Forum statistics

Threads
1,082,298
Messages
5,364,378
Members
400,796
Latest member
vrcdesktop

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top