If not (empty selection) then ...

[sm3117]

Good morning to all,

An easy one..

I have the following code, it works, but it breaks whenever somebody chooses a full column ($A:$A) as it selects all the blank cells and performs all the loop to all cells..

How may the code be adapted in order to work with the selected range EXCEPT blanks?

(My trial is highlighted below)

Sub Compare()


Dim WorkRng1 As Range
Dim WorkRng2 As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim DataRange As Range
Dim ws As Worksheet


xTitleId = "Buscar coincidencias"


Set WorkRng1 = Application.InputBox("Seleccionar equipos con cambios:", xTitleId, "", Type:=8)


    For Each Rng1 In WorkRng1
        rng1value = Rng1.Value
[COLOR=#b22222]        If Not IsEmpty(Rng1.Offset(1, 0)) Then[/COLOR]
            For Each ws In ActiveWorkbook.Worksheets
                LastRow = ws.Range("B1000").End(xlUp).Row
                    Set WorkRng2 = ws.Range(ws.Cells(1, 2), ws.Cells(LastRow, 2))
                        For Each Rng2 In WorkRng2
                             If InStr(Rng2.Value, rng1value) Then
                            'If rng1value = Rng2.Value Then
                            'If Rng2.Value Like "*" & rng1Value & "*" Then
                            Rng1.Interior.Color = VBA.RGB(200, 250, 200)
                        Exit For
                        End If
                    Next
                Next
[COLOR=#b22222]                       End If[/COLOR]
            Next

End Sub

Many thanks,

Have a good day!

 

[L. Howard]

Maybe like this...

Howard

Sub Compare()
Dim WorkRng1 As Range
Dim WorkRng2 As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim DataRange As Range
Dim ws As Worksheet

xTitleId = "Buscar coincidencias"

Set WorkRng1 = Application.InputBox("Seleccionar equipos con cambios:", xTitleId, "", Type:=8)

    For Each Rng1 In WorkRng1
        rng1value = Rng1.Value
        If Not Rng1.Offset(1, 0) Is Nothing Then
            For Each ws In ActiveWorkbook.Worksheets
                LastRow = ws.Range("B1000").End(xlUp).Row
                    Set WorkRng2 = ws.Range(ws.Cells(1, 2), ws.Cells(LastRow, 2))
                        For Each Rng2 In WorkRng2
                             If InStr(Rng2.Value, rng1value) Then
                            'If rng1value = Rng2.Value Then
                            'If Rng2.Value Like "*" & rng1Value & "*" Then
                            Rng1.Interior.Color = VBA.RGB(200, 250, 200)
                        Exit For
                        End If
                    Next
                Next
        End If
    Next
End Sub

 

[sm3117]

Maybe like this...

Howard

Sub Compare()
Dim WorkRng1 As Range
Dim WorkRng2 As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim DataRange As Range
Dim ws As Worksheet

xTitleId = "Buscar coincidencias"

Set WorkRng1 = Application.InputBox("Seleccionar equipos con cambios:", xTitleId, "", Type:=8)

    For Each Rng1 In WorkRng1
        rng1value = Rng1.Value
        If Not Rng1.Offset(1, 0) Is Nothing Then
            For Each ws In ActiveWorkbook.Worksheets
                LastRow = ws.Range("B1000").End(xlUp).Row
                    Set WorkRng2 = ws.Range(ws.Cells(1, 2), ws.Cells(LastRow, 2))
                        For Each Rng2 In WorkRng2
                             If InStr(Rng2.Value, rng1value) Then
                            'If rng1value = Rng2.Value Then
                            'If Rng2.Value Like "*" & rng1Value & "*" Then
                            Rng1.Interior.Color = VBA.RGB(200, 250, 200)
                        Exit For
                        End If
                    Next
                Next
        End If
    Next
End Sub

Nope, same problem...

ANy further help?

 

[wideboydixon]

This line:

    If Not IsEmpty(Rng1.Offset(1, 0)) Then

You're looking at the next row down I suspect you want to check whether that cell is blank. IsEmpty() is not the right function and neither is checking for "Is Nothing". Try this:

    If Rng1.Offset(1, 0).Value <> "" Then

WBD

 

[sm3117]

This line:

    If Not IsEmpty(Rng1.Offset(1, 0)) Then

You're looking at the next row down I suspect you want to check whether that cell is blank. IsEmpty() is not the right function and neither is checking for "Is Nothing". Try this:

    If Rng1.Offset(1, 0).Value <> "" Then

WBD

It works now,

Many thanks!!!!!!!