Check whether certain parts of a spreadsheet have data

Asked

Viewed 51 times

0

I have a spreadsheet that has two parts and I want to check if both have data.

If the Part 1 and the Part 2 have data, the code paints the corresponding yellow line.

If only one of the parties has data, the code does nothing.

Problem:

If the Part 1 has data and the part 2 it doesn’t have, it works.

If both parts have, it also works by painting yellow.

Now, if only the part 2 has dice, he paints yellow anyway.

This is my code:

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Set ws = Worksheets("Plan1")

Dim i, j, largura, linha As Integer
Dim parte1, parte2 As Boolean

parte1 = False
parte2 = False
largura = 22
linha = 2

For i = 2 To 40
   parte1 = False
   parte2 = False
   For j = 11 To largura 'Conta do 11 até 22 (Referência a posição da coluna)

        'verifica as colunas da Parte 1
        If ws.Cells(i, j).Value <> "" Then
            parte1 = True
        End If

        If j > 16 Then 'Inicia a verificação das colunas referentes a Parte 2
            If ws.Cells(i, j).Value <> "" Then
                parte2 = True
            End If
        End If

        'Verifica se ambas as partes possuem dados, se sim, pinta a linha toda de amarelo
        If parte1 = True Then
            If parte2 = True Then
                 ws.Cells(i, j).EntireRow.Interior.ColorIndex = 6
            End If
        End If

   Next j
 Next i

 End Sub

This is the result of that code:Como eu não quero

That’s the result I wanted:

Como eu quero

Something logical in that code is wrong, but I can’t see.

excel vba

  • Your conditions are wrong, check your Ifs. If you are checking the correct columns. Scroll through your code step by step with the F8 key and you will see what happens in your code...

2 answers

1


I think it solves with 

    Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Plan1")

Dim i, j, largura, linha As Integer
Dim parte1, parte2 As Boolean

parte1 = False
parte2 = False
largura = 22
linha = 1

For i = 1 To 40
   parte1 = False
   parte2 = False
   For j = 11 To largura 'Conta do 11 até 22 (Referência a posição da coluna)

        If j < 17 Then
              If ws.Cells(i, j).Value <> "" Then
                parte1 = True
            End If
        End If

        If j > 16 Then 'Inicia a verificação das colunas referentes a Parte 2
            If ws.Cells(i, j).Value <> "" Then
                parte2 = True
            End If
        End If

        'Verifica se ambas as partes possuem dados, se sim, pinta a linha toda de amarelo
        If parte1 = True Then
            If parte2 = True Then
                 ws.Cells(i, j).EntireRow.Interior.ColorIndex = 6
            End If
        End If

   Next j
 Next i
End Sub

because it was not being limited the first if

  • I was testing and I noticed something, now the part 2, even empty, the line is selected

  • The same thing, only the other way around.

  • but you kept your if j>16??? pq here seems to be working...

  • 1

    It worked! Thank you!

1

You can use the for each, as follows:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim sh1 As Worksheet: Set sh1 = ThisWorkbook.Worksheets(1)
Dim sh2 As Worksheet: Set sh2 = ThisWorkbook.Worksheets(2)
Dim val As Boolean: val = False

Set rng = sh1.Range("A1:A20")

For Each cell In rng
    If Not IsEmpty(cell.Value) And _
       Not IsEmpty(sh2.Cells(cell.Row, cell.Column).Value) Then
        val = True
        sh1.Rows(cell.Row).Interior.Color = RGB(250, 255, 63)
        sh2.Rows(cell.Row).Interior.Color = RGB(250, 255, 63)
    End If
    val = False
Next

End Sub

This is just one example of how it can be done.

Note: I couldn’t see the image with the title "as it would be".

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.