Enlcosing code inside if- then no longer works

[Rimmie]

The following works as expected:

ActiveCell.Select
ActiveCell.FormulaR1C1 = "km-KH: HB for TP 1 Build"
Key = Left(ActiveCell, 18)
Sheets("All").Select
'If Key = "Km-KH: HB for TP 1" Then
Cells.Find(What:="km-KH: HO of TP 1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
'End If
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
Sheets("Test").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select

If I don't comment out the If and End If statments, it fails. Note the
correct value gets assigned to the variable Key. What am I doing wrong? Any
help for this novice would be greatly appreciated.

 

[Dave Peterson]

Where is the code located?

Is it behind a worksheet or is it in a General module.

Those unqualified ranges could cause problems.

cells.find(..)
will refer to the activesheet if the code is in a general module, but it refers
to the worksheet that owns the code if the code is in a worksheet module.

I'm not sure if this helps, but maybe you can use this to get further along:


Dim Key As String
Dim FoundCell As Range

ActiveCell.Select
ActiveCell.Value = "km-KH: HB for TP 1 Build"
Key = Left(ActiveCell.Value, 18)

With Sheets("All")
Set FoundCell = .Find(What:="km-KH: HO of TP 1", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If FoundCell Is Nothing Then
MsgBox "Not found on All"
Else
FoundCell.Offset(0, -1).Copy _
Destination:=Worksheets("test").Range("a1")
End If
End With

 

[Don Guillett]

NOT tested but try this from your "Test" worksheet

sub copymatchtonextcolumn()

with ActiveCell
.value= "km-KH: HB for TP 1 Build"
Key = Left(.value, 18)

set copycell=Sheets("All").Cells.Find(What:=key, LookIn:= _
xlvalues, LookAt:=xlwhole, SearchOrder:=xlByRows, SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).address
copycell.Offset(0, -1).Copy .Offset(0, 1)

end with
end sub.