With inside a loop

[JoanaMartins]

I continue to have a Runtime error 91 on this part of my code.
I think it is because the loop includes a with.

Can anyone help me?
Am I putting the right things in the right place?

Dim g As LongDim k As Long




For e = 8 To y


Set x = cs.Range("F:F").Find(fs.Cells(e, 1).Value)




    If Not (x Is Nothing) Then


        primeiracelula = x.Address
        linha = x.Row
        
        Do


        k = Sheets("Crono").Range("E" & linha).Value - Sheets("Crono").Range("D" & linha).Value


        g = fs.Range("6:6").Find(cs.Cells(linha, 4).Value).Column


            
            With Sheets("Final").Range(Cells(e, g), Cells(e, (g + k)))


                .Interior.Color = RGB(255, 153, 204)


            End With
            
        


        Set x = cs.Range("F:F").FindNext(x)
 
        Loop While Not x Is Nothing And x.Address <> primeiracelula
        
     End If
        
    
Next e

Thank you in advance!

 

[My Aswer Is This]

Why not share with us the entire script and tell us what your wanting the script to accomplish.

 

[JoanaMartins]

It is really really long. It has thousands of lines of code that i am constructing over time. It was working ok until i reached this point and it began to give me the error.

Basically at this point I have 2 sheets:

- Sheet("cs") that has on column A a list of activities/actions, on column D the start date and on column F the finish date. The column A may contain duplicates with diferent start and finish dates. For example, a certain exhibition may happen from 3.March until 10.March and again from 1.April until 10.April. This will be in different rows
-Sheet("fs") that has an horizontal bar on row 6 with the dates from 01.01.2016 until 31.12.2016 and, on column A a list of activities, without duplicates.

I need it to paint for example this exhibition that I was talking about on the dates that it happens.


So I was thinking

1 - From the first activitie on sheet fs until the last one
2 - find the activity of sheet fs on sheet cs
3 - if it finds I want it to go to that same new line and subtracts 2 values (it is the finish date and the start date to give me the number of days that this action lasts) - For the example it woould be 8 days
4 - Then I want it to find, on sheet fs row 6 the day that starts and paint, on that line the start date and the 7 consequent days.
5 - I want then loop to find the next date of the same activity. When it finds I want it to do the same thing. subtract the finish and the start and paint the correspondent dates.
6- When it doesn't find the activity anymore I want it to go to the next activity.


In the end I want to have a sheet fs with a list of activities (without duplicates) on column A the dates on the horizontal at the top and the cells painted on the right places.
A very visual map.

Do you think you can help me? Was I clear enough?

 

[My Aswer Is This]

This looks more complicated then I'm able to help you with. I'm sure someone else here at Mr. Excel will be able to help you.

 

[Norie]

Pretty sure the With isn't the problem.

It's more likely that the problem lies here.

        g = fs.Range("6:6").Find(cs.Cells(linha, 4).Value).Column

If Find doesn't find what you are looking for it will return Nothing, and you can't get the Column property of Nothing.

 

[JoanaMartins]

hmm. but if that was the problem it wouldn't run on the first line and paint that and then crash, right?

That is why I think that the problem is the with.
It would not find even the first one right?

 

[netuser]

put a debug.print g and see if you get any value

 

[joeu2004]

I continue to have a Runtime error 91 on this part of my code.
I think it is because the loop includes a with.

In general, there is nothing wrong with putting a With block inside a loop. And I do not see anything structually wrong with the Do loop and With constructs.

However, I do see three potential errors, although I cannot say whether they would raise the particular error that you mention.

1. If the Find fails, Find(...).Column will raise an error, perhaps Error 91.

2. The reference to Range(Cells(e, g), Cells(e, (g + k))) is suspicious, specifically the reference to Cells without a worksheet qualifier. I wonder if that should be Sheets("Final").Cells like Sheets("Final").Range or cs.Cells like Find or fs.Cells like g. Without a worksheet qualifier, Cells refers to ActiveSheet in a normal module or Me in a worksheet object.

3. The expression Not x Is Nothing And x.Address <> primeiracelula will raise an error, perhaps Error 91, if x Is Nothing is true. In that case, x.Address will be an invalid reference. Note that unlike some computer languages, VBA evaluates the entire logical expression; it does not stop sooner if the left part is false.

To correct for #3, make the following changes:

If x Is Nothing Then Exit Do
Loop While x.Address <> primeiracelula

 

[joeu2004]

Another possible programming error: should the statement
linha = x.Row
be inside the loop?
As written, linha does change as x changes.

 

[joeu2004]

Errata (aarrgghh!)....

Another possible programming error: should the statement
linha = x.Row
be inside the loop?
As written, linha does change as x changes.

.... does not change ....