Selection.Find and Range Problem

[esm]

Hello,

Solution Description-
Iterate through a column ("B:B") looking for the value of "Test". Then look at the corresponding date value in the cell to the right. Extract the day ("dd") value, then look at a second specified range and get the column number where the day value is found.

I have a problem with the Rng value being set to "Nothing" after the first time through my Do Loop. If I make the value intColumn a static value, the loop functions as expected.

Any help would be greatly appreciated.

Thank you,
esm

************** Module Code ************
Option Explicit
Sub Mark_cells_in_column()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
'Dim rngDays As Range
Dim sDate As String
Dim sDay As String

Dim I As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Search for a Value Or Values in a range
'You can also use more values like this Array("Test", "Test1")
MyArr = Array("Test")
'Search Column or range
With Sheets("Sheet1").Range("B:B")
'clear the cells in the column to the right
.Offset(0, 2).ClearContents


For I = LBound(MyArr) To UBound(MyArr)
'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "Test"
Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)



If Not Rng Is Nothing Then
FirstAddress = Rng.Address

Do
'read date in column to the right and trim to month value
Dim rngDays As Range
sDate = Rng.Offset(0, 1).Text()
sDay = Format(sDate, "dd")


'Look at row for sDay value and return col number



Set rngDays = Sheets("Sheet1").Range("E2:S2")

rngDays.Select



'You need to zero out the integer variable first so that when the item is not found it remains zero

Dim intColumn As Integer
intColumn = 0

intColumn = Selection.Find(What:=sDay).Column
', _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Column

'intColumn = "14"

If intColumn = 0 Then
MsgBox "Item Not Found"
Else
MsgBox "Item is located at Column: " & intColumn

End If



'mark the cell in the column to the right if "Test" is found
'Rng.Offset(0, 2).Value = "X"


Set Rng = .FindNext(Rng) 'HELP.......This is where the problem is- I think?



Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress

End If
Next I

End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Jerry Sullivan]

Welcome to the Board!

There are a few things in your code that are giving you problems.

1. Duplicate declaration of variables inside a loop. You can only declare each variable once. When you have a Dim statement inside a loop, the code will try to declare the variable twice and generate an error.

Do
    'read date in column to the right and trim to month value
    Dim rngDays As Range
    sDate = Rng.Offset(0, 1).Text()
    '....
Loop While Not...

2. Need to check if a range exists or is Nothing before trying to get its properties.

'You need to zero out the integer variable first so that when the item 
'is not found it remains zero
Dim intColumn As Integer
intColumn = 0
intColumn = Selection.Find(What:=sDay).Column

The comment is incorrect - the code doesn't return 0 if sDay isn't found - it will return an error when it attempts to find the .Column property of a range that doesn't exist.


3. Findnext uses the last .Find criteria
You are intending this statement to find the next "Test" string in Column B.

Set Rng = .FindNext(Rng).

The problem is that the previous .Find method run at that point in the code is actually: .Find(What:=sDay) run earlier in the loop.

You could avoid this by using a function other than Find (such as Application.Match) to find your sDay matches, or you could reorganize your process to first find all the rows with "Test", then find corresponding date columns.

If you run into any difficulties making this work, just post your revised code after trying to address these 3 problems.

 

[esm]

Hi Jerry,

Sorry for the delay in getting back to you. I really appreciate your suggestions, and ended up using Application.Match which worked perfectly. See below-

intColumn = Application.Match(CLng(sDay), Range("AH2:BK2"), 0)

Thank you again for taking the time to reply to my post.

Best Regards,
Eric