Worksheet Change code stuck

[MannStewart]

Hi

1) Can anyone guide me as where I am writing my code wrong? I need my worksheet to do two separate things for two sets of ranges via worksheet_Change event. the top part works, but the latter part doesn't run at all, ie. the part where I need Excel to locate & go to the adjacent cell by Searching in BG1:

Private Sub Worksheet_Change(ByVal Target As Range)



If Intersect(Target, Range("BD2:BD1000")) Is Nothing Or Target.Cells.Count > 1 Then

Exit Sub
Else
Range("BF2:BF1000").Calculate

End If


Dim KeyCells As Range

Set KeyCells = Range("BG1")

Dim FindString As String
Dim Rng As Range
FindString = Range("BG1").Value
If Trim(FindString) <> "" Then
With Range("BC2:BC1000")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
ActiveCell.Offset(0, 1).Select
Else
MsgBox "Not found / Check your entry length & format."
End If
ActiveSheet.Range("BG1").ClearContents
End With
End If

End Sub


2) I also need to write another Worksheet Change code for Excel to jump to the adjacent cell (OFFSET 0,1) whenever I hit ENTER for any cell within the range D2:K1000, but I don't know know to proceed with the above section 2 stuck.

Anyone can Help ..?


Stewart

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]Dim[/color] FindString [color=darkblue]As[/color] String
    [color=darkblue]Dim[/color] Rng        [color=darkblue]As[/color] Range
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Target, Range("BD2:BD1000")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] And Target.Cells.Count = 1 [color=darkblue]Then[/color]
        Range("BF2:BF1000").Calculate
        
    [color=darkblue]ElseIf[/color] Target.Address(0, 0) = "BG1" [color=darkblue]Then[/color]
        FindString = Range("BG1").Value
        [color=darkblue]If[/color] Trim(FindString) <> "" [color=darkblue]Then[/color]
            [color=darkblue]Set[/color] Rng = Range("BC2:BC1000").Find(What:=FindString, _
                                               After:=Range("BC1000"), _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
            [color=darkblue]If[/color] [color=darkblue]Not[/color] Rng [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                Application.Goto Rng.Offset(, 1), [color=darkblue]True[/color]
            [color=darkblue]Else[/color]
                MsgBox "Check your entry length & format.", vbExclamation, "No Match Found"
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            ActiveSheet.Range("BG1").ClearContents
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    [color=darkblue]ElseIf[/color] [color=darkblue]Not[/color] Intersect(Target, Range("D2:K1000")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] And Target.Cells.Count = 1 [color=darkblue]Then[/color]
        Target.Offset(, 1).Select
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MannStewart]

hi AlphaFrog

i tried that, but everytime I input a proper code into BG1 to search, it always just jumps to the BG2 cell below it when it is not supposed to & prompts the msgbox
"Check your entry length & format." This is no matter what code I input, even if the code is correct and actually Matches a content in the range BC2:BC1000.

Can you say why?

Stewart

 

[MannStewart]

hi AlphaFrog

i tried that, but everytime I input a proper code into BG1 to search, it always just jumps to the BG2 cell below it when it is not supposed to & prompts the msgbox
"Check your entry length & format." This is no matter what code I input, even if the code is correct and actually Matches a content in the range BC2:BC1000.

Also, the jump to OFFSET right 1 cell doesn't seem to be working when I hit ENTER for that range.


Appreciate if you can Help me out of this,
Stewart

 

[MannStewart]

hi AlphaFrog

everything works now except the BG1 search part. Do you think it has to do with my number format in BG1 and the range BC2:BC1000? I have kept both same formats, i tried NUMBER, SPECIAL (ZIP CODE 5 digits, as my data code is 5 digits), and GENERAL, but it still doesn't work yet

Stewart

 

[AlphaFrog]

Give an example of what you type in BG1 and what is in BC.