Activate a cell once you find it

[mrfitness]

I am running a search on a variable but when it finds it, I need it to activate that cell and move over 12 spaces. It appears that when he code does find the variable in the worksheet, it moves over 12 columns from cell A1. I need it to select the cell that has the variable in it first. Please help! Thanks in advance

'cont_type has been declared and has a string value already

                        If cont_type = "C" Then
                        'Range("D:D").Select
                        Set rcode = Cells.Find(What:=strcode, After:=ActiveCell, LookIn:=xlFormulas _
                        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
                        
                        
         
                            If Not rcode Is Nothing Then
                                ActiveCell.Offset(0, 12).Select
                                zdisc = ActiveCell.Value
                                Sheets("Surcharges2").Range("E" + Trim(Str(retrow))).Value = zdisc
                                    Else:
                                    MsgBox "There are no discounts for this contract (sheet not found)"
                    
                            End If

 

[brian.wethington]

If cont_type = "C" Then 
                        'Range("D:D").Select 
                        Set rcode = Cells.Find(What:=strcode, After:=ActiveCell, LookIn:=xlFormulas _ 
                        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
                        MatchCase:=False) 
                        
                        
          
                            If Not rcode Is Nothing Then 
                                rcode.Offset(0, 12).Select 
                                zdisc = ActiveCell.Value 
                                Sheets("Surcharges2").Range("E" + Trim(Str(retrow))).Value = zdisc 
                                    Else: 
                                    MsgBox "There are no discounts for this contract (sheet not found)" 
                    
                            End If

Note that it is very rare that you need to select a range to work with it.

 

[mrfitness]

Thanks that worked. I found another way to do it below.Basically I highlight cell A1 first, and if it finds the variable it would activate it, then move off of that cell. So I use an IF saying if the address of the activecell is not A1 then move it from the find.
Which way do you think is better?

                        If cont_type = "C" Then
                        'Range("D:D").Select
                        Cells.Find(What:=strcode, After:=ActiveCell, LookIn:=xlFormulas _
                        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False).Activate
                        
                        
         
                            If ActiveCell.Address <> "A1" Then
                                ActiveCell.Offset(0, 12).Select
                                zdisc = ActiveCell.Value
                                Sheets("Surcharges2").Range("E" + Trim(Str(retrow))).Value = zdisc
                                    Else:
                                    MsgBox "There are no discounts for this contract (sheet not found)"
                    
                            End If
                            
                        End If
                    
                        
                End If
                
                
          Next

 

[brian.wethington]

It is probably going to be best to just ensure that you use the range you found if it exists, for several reasons.

It is better to avoid using select and activate (They tend to cause vastly slower code).

The second code you have presented will error if it does not find the range as you can not activate a range that does not exist.

One more good reason is that activate/select code tends to be confusing, so when asking for help or attempting to rewrite it takes more time.

There are several others reasons but I think you may get the idea.