CODE on Page 70 of: VBA and MACROS (Excel 2010) Book

[lacklogic]

I am curious about one of the code snippets demonstrated on page 70 of the Mr Excel book for VBA & Macros (2010 edition)

Question #1: In the book, the code reads like this: (also, the code here can be found in the downloadable xls files from the website in the file for: Chap 3, Pg 66)

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues)
Rng.Offset(, 1).Value = "LOW"
Sub FindLow()
With Range("B1:B16")
    Set Rng = .Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues)
    If Not Rng Is Nothing Then
        firstAddress = Rng.Address
        Do
            Rng.Offset(, 1).Value = "LOW"
            Set Rng = .FindNext(Rng)
        Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
    End If
End With
End Sub

Are the first two lines of this code meant to be part of the "Declarations" of this SUB ? (Being that the first two lines are above the SUB statement)

Question #2: Can this code be modified in a way that, instead of looking for a "0"...

it could look for a number that is less than, say... five? ( "<5" )

I don't think the ".find" property will allow for this type of argument, but can the "find" be replaced with another property or method that I am not aware of to allow such an alteration to this code, or would it require a fairly heavy duty makeover to act in such a way...? Thanks!!!

Steve

 

[starl]

Sorry, this is what happens when the publisher removes spaces to fit a page...

The first two lines are the code that would do what's described the leading paragraph.

The Sub after those two lines is an example sub with the two lines in action.

No, you can't replace the 0 with <5. That's not how Find works. There are a variety of solutions tho. You could use a spare column with an IF formula, setting a value (such as True) if the value in the other column is less than 5. Then, modify the code to use that formula column for the Find, and modify your offset based off that column.
Or, you could use the AutoFilter to filter for <5 then use SpecialCells visible only to put the Low in only those rows that are only visible.

 

[lacklogic]

Starl, Great and Useful answers! Thanks so much for your response!

Best Regards,

Steve