Check for empty cell in a range of #N/A cells

[Ramo964516]

Hi everybody,


I'm looking for a macro to find the row (in two columns) that contains one of the two criteria: "" or "Productivité".
After finding the first row that contains on of these cells, i'm trying a loop to move down this row. if the next down cell does'nt contain neither "" or "productivité" i have to resize my range to add one row to it.


that is the problem. When i'm testing `Do While Not Range(strDépartPlage).Value Is Nothing And Range(strDépartPlage).Offset(, -1).Value <> "productivité"` it's not working because of the first condition `Do While Not Range(strDépartPlage).Value Is Nothing ` . And it's due to the fact that the cells contains `#N/A`. How can i deal with that ?



Dim strFirstCelVideOuProd As String
Dim rng1erCelVide As Range
Dim rng1erCelProd As Range

Set rng1erCelVide = Range("E3:E60").Find("", Range("E3"))
Set rng1erCelProd = Range("D3:D60").Find("productivité", Range("D3"))

If rng1erCelVide.Row < rng1erCelProd.Row Then
strFirstCelVideOuProd = rng1erCelVide.Address(False, False)
ElseIf rng1erCelVide.Row > rng1erCelProd.Row Then
strFirstCelVideOuProd = rng1erCelProd.Address(False, False)
End If

Dim strDépartPlage As String
count = 0
strDépartPlage = Range(strFirstCelVideOuProd).Offset(1, 0).Address(False, False)




Do While Not Range(strDépartPlage).Value Is Nothing And Range(strDépartPlage).Offset(, -1).Value <> "productivité"
count = count + 1
strDépartPlage = Range(strDépartPlage).Resize(count, 0).Address(False, False)
MsgBox strDépartPlage


Loop

[skywriter]

If you are trying to set ranges based on the blank areas you show in column D there's a faster more efficient way to do it using areas. I could help you but I don't want to recreate your sheet, so if you want to post your workbook instead of a picture I'll help you out.

[Ramo964516]

Hi Skywriter,
Here it is 15567

[Ramo964516]

In other words, I'm trying to extract the range's address between the blank cell and the cell next to the one that contains "Productivité", and the range's address between the cells next to ones that contain "Productivité".

15568

[skywriter]

My code needs to look at the blank spaces you show in column D next to the cells you highlighted in red.

The workbook you sent had a bunch of nonsense letters in those cells so I removed them.

You can run the code and it will highlight each area and show you the addresses, for demonstration purposes only.

If your worksheet isn't blank in those areas as you showed, then we need to think of something else.

I also added one final "Productivite" in cell D41 because I need that for the code also, or I'll have to come up with something else.


Sub Ramo964516()
Dim ws As Worksheet
Dim r As Range
Set ws = Worksheets("Tableau de suivi")
For Each r In ws.Range("D6", ws.Range("D" & ws.Rows.Count).End(xlUp)).SpecialCells(4).Areas
r.Offset(, 1).Select
MsgBox "The address of this area is " & r.Offset(, 1).Address
Next r
End Sub

Run the macro while the "Tableau de suivi" sheet is active to see what happens and let me know how I can further help you.

[Ramo964516]

Hi SkyWriter. Thanks for your answer and your macro. I was trying to understand it.
Your code is quite performant for the sheet that i gave as an exemple, when there are blanks in the column D. But as an exemple i deleted texts in cells. Do you have any idea if there are strings in those cells as given in the sheet below. i have to consider the cells "Productivité" too.


1559115590

[skywriter]

I was using the fact that there was blank cells in column D.
What do you ultimately want to do?
I see you have formulas in column E and that the row with "Productivite" on it is always the last cell with a formula, then there's a blank and then the formulas start again, I could probably use that, but I need to know what you want me to do.

Press F5 and then the special button and you will see formulas, choose formulas and push okay, and that is what I can do in code. I was doing the same thing but it was as if you chose blanks instead of formulas.

Make those spaces in column D between expedition and producttivite blank again, highlight all the cells press F5 and choose blanks and you will see that it only selects the blank cells between the others, that is what I was doing in code.

[Ramo964516]

i have in the same worsheet 4 columns with SUM formula, wich SUM function apply on cells in the previous columns (E to X). For exemple Y7=SUM(E7:I7) and Z7=SUM(J7:N7), AA7=SUM(O7:S7), AB7=SUM(T7:X7)... So i write a code for each of these cells to sum the previous data. And i want to copy these cells to be applied on some of non contigous cells down in the same four columns.

My idea was to detect the ranges in the column E . thoses addresses are in the same row that the four last SUM cells. So i will extract the rows from thos adresses, then i 'll do some changes to generate the ranges for the last four columns.

For exemple, i'll extract E7:E9 then E11:E13 ...---> row7:row9 then row11:row13....--->Y7:Y9 then Y11:Y13 ....---> Range("Y7").Copy range("Y7:Y9, Y11:Y13, Y16:Y17, Y19:Y20, Y22:Y23, Y25:Y26, Y29:Y31, Y34")

....-->....-->....-->Range("Z7").Copy range("Z7:Z9, Z11:Z13, Z16:Z17, Z19:Z20, Z22:Z23, Z25:Y26, Z29:Z31, Z34")

....-->....-->....-->Range("AA7").Copy range("AA7:AA9, AA11:AA13, AA16:AA17, AA19:AA20, AA22:AA23, AA25:AA26, AA29:AA31, AA34")

....-->....-->....-->Range("AB7").Copy range("AB7:AB9, AB11:AB13, Ab16:Ab17, AB19:AB20, AB22:AB23, AB25:Y26, AB29:AB31, AB34")

****There is not always a blank after a cell with "Productivité" in it


My ranges in column E have to be either between the blank cells (those in gray in the worksheet) and the cell with "Productivité" in it (those in blue in this exemple).
Or between two cells with "Productivité" in it.

it doesn't have any relationship with the content of the white and blue cells ( formulas, #N/A, a number, a string...)

[skywriter]

Here's some new code, it's in the attached workbook, try it out and tell me where I'm going wrong.


Sub Ramo964516()
Dim ws As Worksheet
Dim r As Range, strAddress As String
Set ws = Worksheets("Tableau de suivi")
For Each r In ws.Range("E6", ws.Range("E" & ws.Rows.Count).End(xlUp)).SpecialCells(-4123).Areas
If r.Rows.Count > 1 Then
strAddress = r.Resize(r.Rows.Count - 1, 1).Address
Range(strAddress).Select
MsgBox "The address of this area is " & strAddress
End If
Next r
End Sub

[Ramo964516]

Thanks Skywriter,
It doesn't select the right ranges. The right ranges does not include "blue" cells.

[skywriter]

Run this code in the attached sheet.


Sub Ramo964516V2()
Dim ws As Worksheet
Range("ZZ:ZZ").ClearContents
Range("ZZ6").Value = Range("D6").Value
Dim r As Range, strAddress As String
Set ws = Worksheets("Tableau de suivi")
For Each r In ws.Range("D7", ws.Range("D" & ws.Rows.Count).End(xlUp))
If r.Value = "Productivité" Or r.Offset(, 1).Formula = "" Then Cells(r.Row, "ZZ").Value = "Test"
Next r
For Each r In ws.Range("ZZ6", ws.Range("ZZ" & ws.Rows.Count).End(xlUp)).SpecialCells(4).Areas
r.Offset(, -697).Select
MsgBox r.Offset(, -697).Address
Next r
End Sub

[Ramo964516]

That one works perfectly. That's exactly what i'm looking for. Please, can you explain to me what did you do in your code ?

[skywriter]

I picked a column ZZ that I assume you won't be using since it's so far away from your data.

First in ZZ6 I put the same value that's in D6 so that ZZ6 won't be blank.

Then I loop down through the cells in column D and check if the cell has Productivite in it and I also check if the cell in column E of the same row is empty.

If either of those conditions are true then I put the word test in column ZZ of the same row, again it's just so that the cell isn't blank.

After all of that I loop through the ZZ column and use special cells of the blank type in combination with areas, which is each contiguous area of blank cells.

So if you run the code and look in column ZZ you will see blank areas, between words, that coincide with the areas you want addresses for in the E column.

I then use offset to get the addresses of the same areas but from the E column not the ZZ column.

If you need further help utilizing the addresses then explain to me what you want to do with them and I'm sure I can help you.

[Ramo964516]

It's all clear now. Thanks a lot, you've been very helpful.

[skywriter]

My pleasure, thanks for the feedback.
:beerchug: