Issue with Find in my script

most

Board Regular
Joined
Feb 22, 2011
Messages
87
Platform
Windows, Mobile
This works fine, but I rather not use .Select
Code:
Range("F13:F22").Select
For Each c In Range("F25:F33").Cells
 If Not IsEmpty(c.Value) Then
    Set FoundCell = Selection.Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not FoundCell Is Nothing Then c.Offset(0, 1).Value = FoundCell.Offset(0, 2).Value + c.Offset(0, 1).Value
 End If
Next c
So I thought this would work, but it doesn't.
Code:
With Range("F13:F22")
  For Each c In Range("F25:F33").Cells
    If Not IsEmpty(c.Value) Then
      Set FoundCell = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
      If Not FoundCell Is Nothing Then c.Offset(0, 1).Value = FoundCell.Offset(0, 2).Value + c.Offset(0, 1).Value
    End If
  Next c
End With
Neither does this.
Code:
For Each c In Range("F25:F33").Cells
 If Not IsEmpty(c.Value) Then
    Set FoundCell = Range("F13:F22").Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not FoundCell Is Nothing Then c.Offset(0, 1).Value = FoundCell.Offset(0, 2).Value + c.Offset(0, 1).Value
 End If
Next c
The result of both are that the values are not updated.
The purpose of the script is the find all values from F25:F33 in F13:F22 and update G25:G33 with value from H13:H22.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
All three of those codes should work.
Try stepping through the code line by line using F8 to see what is happening.
 

most

Board Regular
Joined
Feb 22, 2011
Messages
87
Platform
Windows, Mobile
Find doesn't find =)
Added some debug lines and as this script show, there is something wrong with
Set FoundCell = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
The result seems to be "nothing". Only conclusion I have is that 'With' doesn't compute with '.Find' in this situation.
Code:
With Range("F13:F22")
  For Each c In Range("F25:F33").Cells
   Debug.Print "1: " & c.Address, c.Value
    If Not IsEmpty(c.Value) Then
      Set FoundCell = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
      If Not FoundCell Is Nothing Then c.Offset(0, 1).Value = FoundCell.Offset(0, 2).Value + c.Offset(0, 1).Value
      If Not FoundCell Is Nothing Then Debug.Print " F: " & c.Offset(0, 1).Value, FoundCell.Offset(0, 2).Value, c.Offset(0, 1).Value
    End If
  Next c
End With
Debug.Print "---------------------"
Range("F13:F22").Select
For Each c In Range("F25:F33").Cells
Debug.Print "2: " & c.Address, c.Value
 If Not IsEmpty(c.Value) Then
    Set FoundCell = Selection.Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not FoundCell Is Nothing Then c.Offset(0, 1).Value = FoundCell.Offset(0, 2).Value + c.Offset(0, 1).Value
    If Not FoundCell Is Nothing Then Debug.Print " F: " & c.Offset(0, 1).Value, FoundCell.Offset(0, 2).Value, c.Offset(0, 1).Value
 End If
Next c
Code:
1: $F$25      Test1
1: $F$26      Test2
1: $F$27      
1: $F$28      
1: $F$29      
1: $F$30      
1: $F$31      
1: $F$32      
1: $F$33      
---------------------
2: $F$25      Test1
 F: 55500      1500          55500 
2: $F$26      Test2
2: $F$27      
2: $F$28      
2: $F$29      
2: $F$30      
2: $F$31      
2: $F$32      
2: $F$33
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
As I said before all three of the codes you posted work.
What else do you have in that sub?
 

most

Board Regular
Joined
Feb 22, 2011
Messages
87
Platform
Windows, Mobile
As I said before all three of the codes you posted work.
What else do you have in that sub?
There are no more code in the sub.

Some of the cells are merged, but the second code I posted should prove that there are no data issue. Since both code streams use same data, right!?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
Some of the cells are merged
That is the problem, NEVER use merged cells, they are an abomination & should be avoided like the plague.
 

most

Board Regular
Joined
Feb 22, 2011
Messages
87
Platform
Windows, Mobile
That is the problem, NEVER use merged cells, they are an abomination & should be avoided like the plague.
Hahaha, yeah, starts to realize that now. Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,095,745
Messages
5,446,251
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top