VB Code Navigating to Next Available Worksheet in Range

[Randy11]

Am looking for assistance with some VBA code.

Have a range name set up that is Cell B2 on Sheet1:Sheet10. Range is Named "UnitNumber"

Have placed button control on a menu worksheet that would like to open a InputBox("Key in Unit Number")

If unit number has already been keyed in B2 in range Sheet1:Sheet10 Cell B2, jump to that sheet and land in cell B5.

If unit number has not yet been entered into range Sheet1:Sheet10 Cell B2, then go to next sheet in range with nothing in Cell B2 and place unit number keyed in UnputBox in Cell B2, then move to cell B5.

Currently have code for navigating that is based on Worksheet name and this will not work for what I am trying to achieve.

Your assistance Appreciated....

 

[SkipVought]

hi,

Currently have code...

And????



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Apologies, thought I had pasted in the current code the utlizes worksheet tab name.

Sub Get_Unit_Sheet()
'
' Get_Unit_Sheet Macro
' Navigate to Unit Sheet
'
' Keyboard Shortcut: Ctrl+s
'

Dim SearchData As String

SearchData = InputBox("Key in Unit Number")

If SearchData <> vbNullString Then
On Error Resume Next
Sheets(SearchData).Activate
Range("B2").Select
If Err.Number <> 0 Then MsgBox "Unable to find Unit number: " & SearchData
If Err.Number <> 0 Then MsgBox "Add New Unit"
On Error GoTo 0
Range("b2").Select
End If
End Sub

 

[SkipVought]

If unit number has already been keyed in B2 in range Sheet1:Sheet10 Cell B2, jump to that sheet and land in cell B5.

If unit number has not yet been entered into range Sheet1:Sheet10 Cell B2, then go to next sheet in range with nothing in Cell B2 and place unit number keyed in UnputBox in Cell B2, then move to cell B5.

I'm not sure that I understand this logic.

So if you find the SearchData in Sheet1, then you end up on Sheet1!B5.

If it cannot be found on Sheet1, then look in Sheet2 and if found you end up in Sheet2!B5.

See what is happening? So, if I'm on Sheet10 and cannot find the SearchData, then what happens?

A more fundamental question, why 10 sheets? What's the purpose?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Thanks Skip, will take another go at the logic intent.
Look at Sheet1:Sheet10 Cell B2
If unit number keyed is in one of the 10 sheets (Sheet1:Sheet10) in B2, go to that sheet land in Cell B5
If unit number is not in cell B2 of Sheet1:Sheet10, then go to the first sheet in range with nothing in B2, place the New Unit Numer in B2 & Jump to Cell B5.
You bring up a good point, there also should be an "if" all Sheets in Range Sheet1:Sheet10 B2 have a unit number, MsgBox "Create new Unit Sheet" would work for now, can always add code to this if needed. Is a very unlikely scenario in this situation.

 

[SkipVought]

Try this

Sub Get_Unit_Sheet()
'
' Get_Unit_Sheet Macro
' Navigate to Unit Sheet
'
' Keyboard Shortcut: Ctrl+s
'

    Dim SearchData As String, ws As Worksheet, rFound As Range, bFound As Boolean
    
    SearchData = InputBox("Key in Unit Number")
    
    bFound = False
    
    If SearchData <> vbNullString Then
'        On Error Resume Next
        For Each ws In Worksheets
            Set rFound = ws.Cells.Find(What:=SearchData, After:=ws.[A1], LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
            If Not rFound Is Nothing Then
                ws.Activate
                bFound = True
                Exit For
            End If
        Next
        
        If bFound Then [B5].Select
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Will need to identify the range in order for this to work. This jumps to other sheets in the workbook. It needs to look at range of sheets identified.

 

[PHV]

A starting point:

Sub Get_Unit_Sheet()
    SearchData = InputBox("Key in Unit Number")
    If SearchData = vbNullString Then Exit Sub
    j = 0
    For i = 1 To 10
        With Worksheets("Sheet" & i)
            If .Range("B2") = SearchData Then
                .Activate
                .Range("B5").Select
                Exit Sub
            End If
            If Trim(.Range("B2")) = "" And j = 0 Then j = i
        End With
    Next
    If j > 0 Then
        With Worksheets("Sheet" & j)
            .Activate
            .Range("B2")= SearchData
            .Range("B5").Select
            Exit Sub
        End With
    End If
    MsgBox "Create new Unit Sheet"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Randy11]

This worked excellent with one snag. if a Unit number already exists on one of the sheets, need to go to the one that is currently there and drop into B5. This is set up to go to a new sheet each time.
Appreciate your help, a little more & I am there.....

 

[SkipVought]

So have you been playing with the code that has been posted for you?

What have you done? What are the symptoms?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Thanks for the follow up Skip.
Have adopted the code provided by PHV (Thank you).
Still have one problem. If a Unit number os already on one of the sheets, it still hops to an unused sheet in the range, places the unit number in B2 then jumps to cell B5.
Am trying to understand the logic in the following part of the code as I believe this is where the issue is.
As I read this it appears that if search data is found to Activate but is not activating the current sheet where Unit Number is found. It is going to a new sheet & placing searched vaule in B2. Have tried a few things, a (-1) to go back or activate current sheet etc.

With Worksheets("Sheet" & i)
If .Range("B2") = SearchData Then
.Activate
.Range("B5").Select
Exit Sub
End If
If Trim(.Range("B2")) = "" And j = 0 Then j = i
End With

 

[SkipVought]

In the VB Editor is a STEP feature (F8).

STEP thru your procedure to observe what is happening.

Use the Watch Window to observe values of variables, faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Am still struggling with this. Have Run the F8 with Wathc Window. The Code Highlights one row at a time in yellow. No matter what is searched, it runs the same path.
Starts at Sub line Search Box opens, key in value & enter, moves to: If .Range("B2") = SearchData Then
then jumps to End If... Then jumps to If Trim, then End With, then Next,
Jummp back up to With Worksheets("Sheet" & i)

Sure looking forward to a course or two this year. Looks like it should work but clearly is not. Ideas?

 

[SkipVought]

Well that is only ONE trip thru the loop.

Did you follow it further?

Did you look at counter values or other variable values that are changing?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

No matter what was searched it looped the same way until i = 11 (so searched Sheets 1 - 10) J = 1

Further testing, if the 1st sheet in the range has the unit number you key in, it will go to that sheet. If the unit number you key in is in any sheet other than sheet1 the next open sheet is choosen & set up causing a duplicate unit number.

Ideas?
Sorry, knowledge level not that good on the Watch Window feature but getting the ideaa.

 

[Randy11]

PHV, Code is great, ideas on the snag?

 

[Randy11]

Any Ideas on a solution to this snag.
Would like to search sheet 1 to 10 & look in cell B2. If searched value is found navigate to that sheet & land in Cell B5. If value searched is not found, go to next available sheet in sheet1 - Sheet10 Place searched value in B2 on the unused sheet & jump to B5.
Currently the code will go to next blank sheet regardless if searched value already exists. Any help appreciate.


Sub Get_Unit_Sheet()
SearchData = InputBox("Key in Unit Number")
If SearchData = vbNullString Then Exit Sub
j = 0
For i = 1 To 10
With Worksheets("Sheet" & i)
If .Range("B2") = SearchData Then
.Activate
.Range("B5").Select
Exit Sub
End If
If Trim(.Range("B2")) = "" And j = 0 Then j = i
End With
Next
If j > 0 Then
With Worksheets("Sheet" & j)
.Activate
.Range("B2")= SearchData
.Range("B5").Select
Exit Sub
End With
End If
MsgBox "Create new Unit Sheet"
End Sub

 

[SkipVought]

???

It works perfectly for me!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Still not functioning se needed.
Here is test set up:
Sheet1 B2 = 12205
Sheet2 B2 = 99034
Sheet3 B2 = 22563
On menu page used control button to launch input box
Searched for 12205
Result should have navigated into Cell B5 on Sheet1 as location already exists
Actual Result Sheet4 B2 got 12205 (searched location) and jumped in cell B5
If the location is exisiting it should not create another.
This result work perfect when there is a new location. It finds next available sheet & set it up.

 

[SkipVought]

If that is the case, then your cell does NOT simply contain 12205.

Check for leading or trailing space or unprintable character.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}