Macro to jump to next blank with a criteria

hi

I have a form button that I need the cursor to jump to the next blank in the column BP9:BP, where the first data row starts from BP9, and user inputs data non-contiguously in column BP9:BP (column 68). The mandatorily contiguous input range "Date" column D determines last working row. The find criteria is to jump to the NEXT BLANK in BP9:BP which ALSO is accompanied with a FILLED NUMERIC VALUE in its adjacent column BO (on same row with BP). For example, some BP9:BP may be blank but its adjacent BO could be filled with a TEXT value, or BO could be just empty, (these types of blank BP9:BP should just be skipped and not to be bothered with by the macro). BO9:BO is auto-calculated by formula, currently has formulae (that isn't dependent on BP), down to row 10,000, & on workbook open auto-calculation if D is filled, it returns a value that may be TEXT or NUMERIC, or returns a blank.

The find criteria is only above, go for the next BP blank starting from BP9, stop at the next BP that is blank, that has an adjacent BO that is filled & BO is a NUMERIC value.
I have an existing code that does a simpler criteria by finding for the next blank in AW9:AW as follows but I didn't have the capability to adapt this code (i think it's the blue bit) to fit the above criteria. If anyone can show me how the modification should be done, I would appreciate it.

Sub RECONOPEN_Rick()


   Dim LastRow As Long, PrevCell As Range
   Set PrevCell = ActiveCell
   LastRow = Cells(Rows.Count, "D").End(xlUp).Row

  'AW8 has a formula that derives from the ranges DNR_Register_rec_ctr_opn RECON_chkr_OPEN that counts the no.of blanks in AW9:AW
    Range("DNR_Register_rec_ctr_opn").Calculate
    Range("RECON_chkr_OPEN,AW8").Calculate
    If Range("AW8").Value = 0 Then
     MsgBox "    No unrecon OPEN", vbOKOnly, "     Browse for Unrecon"
     Exit Sub
    Else
    
    If ActiveCell.Column <> 49 Then
     Range("AW9:AW" & LastRow).Find("", Cells(LastRow, "AW"), xlValues, xlWhole, , xlNext, , , False).Select
     If ActiveCell.Row >= LastRow Then
      PrevCell.Select
      MsgBox "    No unrecon OPEN", vbOKOnly, "     Browse for Unrecon"
     End If
    Else
     Range(ActiveCell, Cells(LastRow, "AW")).Find("", ActiveCell, xlValues, xlWhole, , xlNext, , , False).Select
     If ActiveCell.Row >= LastRow Then
      PrevCell.Select
      MsgBox "    No more unrecon OPEN", vbOKOnly, "     Browse for Unrecon"
     End If
    End If
    
   End If
  
End Sub

cheers
Stewart

hi there

so i tried your code, but it stops only at the first blank and won't move anymore, and it also displays the msgbox at the same time too that there are "No matching blanks", when it shouldn't because there are still blanks to be found below. Maybe somewhere along the 4th paragraph needs rearranging ?

Stewart

Hi, Sorry the message box was a mistake.

How about this?

It looks for blanks in the column BP where BO has a number in it.

Sub ActivateNextBlank()
Dim rngOfInterest As Range, rngOfBlanks As Range, cell As Range
Dim LastRow As Long
Dim blnFound As Boolean
Static rngLastFoundBlank As Range


LastRow = Cells(Rows.Count, "D").End(xlUp).Row

If rngLastFoundBlank Is Nothing Then
    Set rngOfInterest = Range("BP9:BP" & LastRow)
Else
    Set rngOfInterest = Range("BP" & rngLastFoundBlank.Row + 1 & ":BP" & LastRow)
End If

On Error Resume Next
If rngOfInterest.Cells.Count = 1 Then
    If rngOfInterest.Value = "" Then
        Set rngOfBlanks = rngOfInterest
    Else
        Set rngOfBlanks = Nothing
    End If
Else
    Set rngOfBlanks = rngOfInterest.SpecialCells(xlCellTypeBlanks)
End If
On Error GoTo 0

If Not rngOfBlanks Is Nothing Then
    For Each cell In rngOfBlanks.Cells
        If cell.Offset(0, -1).Value <> "" And IsNumeric(cell.Offset(0, -1).Value) Then
            cell.Activate
            Set rngLastFoundBlank = cell
            blnFound = True
            Exit For
        End If
    Next
    If Not blnFound Then
        If MsgBox("No more matching blanks. Reset?", vbYesNo + vbQuestion) = vbYes Then
            Set rngLastFoundBlank = Nothing
        End If
    End If
Else
    MsgBox "No blanks"
End If
End Sub

hi

it performs erratically this time.

This is the peculiar situation:

at first run, it seemed to work, jumping from 1st blank to next & onto the next till the last, but i noticed you added the msgbox " ... Reset ?" After that it didn't work anymore. It just jumps straight to the last blank that meets the criteria at the last row of the sheet, bypassing all other blanks that also meet the criteria above it. Once user goes on working with inputting the sheet, and at a later point goes back to click it again, it won't work Maybe it's better off without the Reset question, and just display the msgbox "No more unrecon" instead ?

I don't know if this msgbox is an apt idea, because there is no need to ask the user, i think the best is to do without it. To test if it was due to choosing No previously, i tried removing the section with the reset msgbox, at 1st run it worked again, but when repeating the exercise it didn't work anymore just like previously when it was with the Reset msgbox. So, i think it's not the msgbox that failed it. What's the reason for this ?

hi

it's still the same, despite without the question.

At first run, it works, till the last blank. After that, it will not work anymore. It just always jumps straight to the last blank thereafter.

Perhaps it might be a structural issue with the code? although i'm no expert with codes. I'm thinking, the code needs to go back to the start after reaching the last blank BP that meets criteria? I mean, it has to allow user to keep repeating (START ALL OVER AGAIN) the process every time it has finished running through the range BP9:BP lastrow

if i did accordingly as you suggested, it wouldn't run after deleting

Static rngLastFoundBlank As Range

even with

Private rngLastFoundBlank As Range

separately mentioned

I don't know how it is working for you, do you mean you can do it over & over again after reaching the last blank BP that meets criteria? If after you reached the end, then if you put your activecell somewhere on the sheet, and you clicked the macro again, it would start at the first blank BP again ?

That's awfully strange then / i wish i could but it's too big a file

I've kept retrying many times with your code but still same result, i don't know how to go from here.


Is there any way you can help me modify the code (essentially the blue bit) i first posted because that code worked for my workbook, only the part for the criteria needed tweaking
I'm hoping at least I can have something that can help to meet ongoing work and get through the week. Kindly see if you can help ?

Stewart

You could also use this event macro* on the tab in question:

Option Explicit
Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False
    Range("BP9").Select 'Starting cell address when the tab is selected. Change to suit if necessary.
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

* Follow these four steps to put the above code on the desired tab:

1. Copy my code to the clipboard (Ctrl + C)
2. Right click on the relevant tab name and from the shortcut menu select View Code
3. Paste (Ctrl + V) my code you copied to the clipboard from step 1
4. From the File menu click Close and Return to Microsoft Excel

hi Robert

Great to hear from you. But what you suggested was 1 of the things i had first tried (days ago), & it didn't resolve the issue. I even tried putting that on the top of the code other than the Sheet. No change

I don't have a deep command of VBA but maybe the macro really is hinged on the variables like what ByteMarks said. So, maybe the solution is to reposition / reconfigure the code about how the variables can be released from the last address no matter which Sheet the user chooses to jump to & fro between Sheets?

Stewart

Though clunky try this which I have based on my solution (post #15) to your original thread from here:

Option Explicit
Sub RECONOPEN()

    Const varPwd As Variant = "Hi There" 'Password for protecting and unprotecting the active sheet. Change to suit.
    Const strSrcCol As String = "BP" 'Data column.
    Const lngStartRow As Long = 9 'Initial data row.

    Dim rngBlanks As Range
    Dim lngRowFrom As Long, lngLastRow As Long, i As Long, j As Long
    Dim varData As Variant
    
    Application.ScreenUpdating = False
    
    If Range(strSrcCol & lngStartRow - 1).Value = 0 Then
        MsgBox "No unrecon OPEN", vbExclamation, "Browse for Unrecon"
        Exit Sub
    End If
    
    ActiveSheet.Unprotect Password:=varPwd
    
    Range("DNR_Register_rec_ctr_opn").Calculate
    Range("RECON_chkr_OPEN,AW8").Calculate
    
    lngLastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row 'The date column D still determines lastrow
    
    'Count applicable cells
    For i = IIf(ActiveCell.Row = lngStartRow, lngStartRow, ActiveCell.Row + 1) To lngLastRow
        If Len(Range(strSrcCol & i)) = 0 And Len(Range(strSrcCol & i).Offset(0, -1)) > 0 And IsNumeric(Range(strSrcCol & i).Offset(0, -1)) = True Then
            j = j + 1
        End If
    Next i
    
    'If there are no more applicable cells in column 'strSrcCol, then...
    If j = 0 Then
        MsgBox "No more unrecon OPEN", vbExclamation, "Browse for Unrecon" '...inform the user
        Application.ScreenUpdating = True '...turn screen updating back on
        ActiveSheet.Protect Password:=varPwd, DrawingObjects:=True, Contents:=True, Scenarios:=True '...protect the activesheet
        Exit Sub
    End If
    
    'If there are more applicable cells scroll through until the next is found and step there
    For i = IIf(ActiveCell.Row = lngStartRow, lngStartRow, ActiveCell.Row + 1) To lngLastRow
        If Len(Range(strSrcCol & i)) = 0 And Len(Range(strSrcCol & i).Offset(0, -1)) > 0 And IsNumeric(Range(strSrcCol & i).Offset(0, -1)) = True Then
            Range(strSrcCol & i).Select
            Exit For
        End If
    Next i
    
    ActiveSheet.Protect Password:=varPwd, DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

hi Robert

i've copied & applied your code above, but for some reason it doesn't work & on click just jumps straight to cell DV6 and stays there forever.. ie. further clicks won't move it (DV6 is not locked, so that may be why your macro consequently landed there, but why it chose to go there i don't know)

(sorry about previous wrong message it was Bytemark's code that is working just not going to the top as I needed, I was switching your code and his for the button to compare & find the reason why your code isn't working)


Stewart

hi ByteMarks

but it cannot run because in post#16 you gave me a

Sub ActivateNextBlank()

if I assign the macro to ActivateNextBlank with a Dim on the top EXCEL says ONLY COMMENTS MAY APPEAR AFTER END SUB, END FUNCTION OR END PROPERTY.

or i must be doing something wrong to overwirte the original code, if you could explain to me how to correctly again ..

Stewart

Originally Posted by jindon

Each click of what?

So, this macro is for a form button. I need with each click of this button i need to send the activecell to the NEXT cell in the range BP below the current one, that meets the search criteria.

the criteria is simple: the next cell in BP that is blank, and its adjacent BO has a numeric data (BO contains TEXTs, NUMERICs, blanks)

Stewart

Originally Posted by jindon

See if this is how you wantd.

Sub test()
    Dim LR As Long, x
    Static n As Long
    LR = Range("d" & Rows.Count).End(xlUp).Row
    x = Filter(Evaluate("transpose(if((bp9:bp" & LR & "="""")*(bo9:bo" & LR & "<>"""")" & _
                "*(isnumber(bo9:bo" & LR & "+0)),row(9:" & LR & ")))"), False, 0)
    If UBound(x) = -1 Then MsgBox "No cell available": n = 0: Exit Sub
    If UBound(x) < n Then n = 0
    Cells(x(n), "bp").Select: n = n + 1
End Sub

wow ... so short yet yes it does

ok, it essentially is doing what it's supposed to, only 2 issues left, that is, your MSGBOX doesn't seem to be working when last reached, and that issue that i would like it NOT to still dwell in the last searched location when user has left the Sheet for another, ie, i need the search to start afresh from the top of BP9 when user returns to main Sheet from another Sheet

Stewart

Originally Posted by jindon

This doesn't help.
How is it not working?

maybe i thought your MSGBOX was for a warning to user when last criteria-satisfied cell is found, i mean if your MSGBOX is for when no cell meets criteria then it's fine. If i need a MSGBOX for warning to user when last criteria-satisfied cell is found, where should that be inserted?

To the worksheet code module.

Option Explicit

Private n As Long, x

Private Sub Worksheet_Activate()
    n = 0: x = Empty
End Sub

Sub test()
    Dim LR As Long, x
    LR = Range("d" & Rows.Count).End(xlUp).Row
    If IsEmpty(x) Then
        x = Filter(Evaluate("transpose(if((bp9:bp" & LR & "="""")*(bo9:bo" & LR & "<>"""")" & _
                    "*(isnumber(bo9:bo" & LR & "+0)),row(9:" & LR & ")))"), False, 0)
    End If
    If UBound(x) = -1 Then MsgBox "No cell available": n = 0: x = Empty: Exit Sub
    If UBound(x) < n Then MsgBox "This is the Last cell": n = 0: x = Empty: Exit Sub
    Cells(x(n), "bp").Select: n = n + 1
End Sub

hi Jindon

the addition of the MSGBOX is good now, but that

Option Explicit

Private n As Long

is causing a line in my Private Sub Worksheet_Change(ByVal Target As Range)

specifically, the line

    ThisRow = Target.Row

to malfunction with an Error message COMPILE ERROR VARIABLE NOT DEFINED. The Worksheet Change was working fine all along even without defining it (before adding your above), now how should I work around this after adding..? After adding the Option Explicit, now every time i make any input change to the Sheet, this error pops up.

Even as I define it as Long, EXCEL looks for other variables (1-by-1) to call out the same Error msg complaining they are not defined, and I have quite a number of variables that were working nicely without being defined.
Any workaround ..?

Ahh, I didn't read it till the end.

Add one line in you Worksheet_Change code

Dim ThisRow As Long

Originally Posted by jindon

Ahh, I didn't read it till the end.

Add one line in you Worksheet_Change code

Dim ThisRow As Long

yea, i did that like i mentioned, even as I define it as LONG under Worksheet.Change, EXCEL would just pick out other variables (1-by-1) to call out the same Error msg complaining they are not defined, and I have quite a number of variables that were working nicely without being defined. Did you mean there's another place i should define or there's no way ..? And, just supposing that I do exhaustively define ALL variables that exist on the main Sheet, would that make the error go away completely ? And should every variable be defined as LONG?

Originally Posted by jindon

Can you post your code?

i tried but i don;t know if it's because the code is too long (amateurishly long) mainly because it consists of many bits & parts i put together myself that i couldn't submit it here. It just gets stuck in Preview although i clicked Submit, why is this

Is it alright if i PM you the code ?

And, would the EXCEL UNDEFINED VARIABLE error also affect all other codes under other sheets if I went ahead with your Private n as Long ?

i've copied & applied your code above, but for some reason it doesn't work & on click just jumps straight to cell DV6 and stays there

I can't see how that could happen as nowhere in the code do I tell it to land anywhere in Col. DV

I see jindon has taken up cause so you are getting some of the best support possible so I'll humbly bow out at this stage.

Good luck with it.

Robert

Originally Posted by jindon

NO, it MUST be placed in the same WORKSHEET CODE MODULE.
I said,

ok, i see it, there's a "Sheet1" preceding the macro name, i didn't knowi can place like this as I never tried this way before. let me try and see if this works this time

the skipping has gone

but it still won't go to the range top to start the search when returned to Sheet

My workbook is set to manual mode calculation as there are too many formulae and sheets, is that a factor ?