vba find and print an array

[Giordano Bruno]

I have a worksheet which comprises a rectangular grid of data always starting in A1 and which is curently showing either TRUE or FALSE, but which could be changed to any pair of responses, e.g. 1 or "". I wish to find all cell references where the result is FALSE or whatever and print the list of cell references on another worksheet in the same workbook. In this case it is Sheet4.

Any assistance will be greatly welcomed.

 

[Richard Schollar]

How would you want the cell addresses formatted? eg all in a single cell in comma separated list? Or in a vertical list each address in its own cell?

 

[Giordano Bruno]

Hi Richard,

Thanks for the prompt reply. My first preference would be a vertical list with each sell reference in its own cell.

 

[Richard Schollar]

Here's one way:

Sub Gen_Addresses()
Const VAL_TO_FIND = False  'amend as appropriate
Dim rng As Range, cell As Range
Dim wsNew As Worksheet
Dim strFirstAddress As String
Set rng = Range("A1").CurrentRegion
Set wsNew = Worksheets.Add
wsNew.Name = "Output"
wsNew.Range("A1") = "Address of cell"
If rng(1) = VAL_TO_FIND Then
    wsNew.Range("A2") = rng(1).Address
End If
'the xlByColumns ensures output of all row values in first column first followed by rows in second col etc.  Change to xlByRows if you want all the applicable columns in the first row followed by second row, third etc:
Set cell = rng.Find(what:=VAL_TO_FIND, After:=rng(1), searchorder:=xlByColumns, searchdirection:=xlNext)   
If Not cell Is Nothing Then
    strFirstAddress = cell.Address
    With wsNew
        Do
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = _
                cell.Address
            Set cell = rng.FindNext(cell)
        Loop Until cell.Address = strFirstAddress
    End With
End If
End Sub

 

[Giordano Bruno]

Thanks Richard.

Unfortuately I forgot to mention that the TRUE and FALSE were the result of a formula like x=y, i.e. not text "True" or "False". I think that's why it can't find any FALSE cells

Can you tell me what to do to fix this?

 

[Richard Schollar]

Is it an actual TRUE or FALSE result? It should work, but the following code incorporates specific arguments to make sure the Find works on cell values and looks at the whole value:

Sub Gen_Addresses()
Const VAL_TO_FIND = False  'amend as appropriate
Dim rng As Range, cell As Range
Dim wsNew As Worksheet
Dim strFirstAddress As String
Application.DisplayAlerts = False

Set rng = Range("A1").CurrentRegion
Set wsNew = Worksheets.Add
On Error Resume Next
Worksheets("Output").Delete
On Error GoTo 0
wsNew.Name = "Output"
wsNew.Range("A1") = "Address of cell"
If rng(1) = VAL_TO_FIND Then
    wsNew.Range("A2") = rng(1).Address
End If
Set cell = rng.Find(what:=VAL_TO_FIND, After:=rng(1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
If Not cell Is Nothing Then
    strFirstAddress = cell.Address
    With wsNew
        Do
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = _
                cell.Address
            Set cell = rng.FindNext(cell)
        Loop Until cell.Address = strFirstAddress
    End With
End If
End Sub

 

[Giordano Bruno]

This is strange Richard.

It works in a test worksheet that has the formula =MOD(ROW(),3)=0. But I have a large sheet which compares two other worksheets with the formula ='ver5'!A1='ver6'!A1 copied out to cell BA951 and containing about 20 FALSE results yet when applied to this worksheet it finds none of them.

The code goes as far as the line "If Not cell is Nothing then" and skips to End If

If I create another worksheet with a formula to return the value in the cells in the first worksheet, thus recreating the pattern of TRUE and FALSE cells out to BA951 and run the macro on this worksheet, it works, but when I try i again on the original worksheet it finds nothing.

It's got me tossed, but thanks very much for the macro. I can probably either fix the problem or find a work-around.

 

[Richard Schollar]

I wonder if that's because the CurrentRegion isn't returning the entire range - on the one where it doesn't work, just type this in in the Immediate Window in the VBE (Ctrl+G to open the Immediate Window if it isn't already):

?[A1].CurrentRegion.Address

don't omit the leading ?. If this doesn't return the expected range, then we have identified the problem!

You need this worksheet visible on the Excel screen when you try the above.

 

[Giordano Bruno]

Richard,

I have closed down Excel and opened it again. When I added the Immediate window it found the correct range and when I ran the code it worked fine.

Whether it will work a second time now that I have thrown the computer to the floor and jumped on it is another matter.

Many many thanks for your patience and valuable assistance.

 

[Giordano Bruno]

A brief postscript to this thread. I have noticed that it will not find a Boolean FALSE if the cell width is insufficient to display the text and it is replaced by a line of #'s.