Trying to implement a messagebox if data is not numeric

[TheJay]

I have code that checks for gaps and fills them in with sequential numeric data.

Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
    Loop Until F
End Sub

I'm trying to introduce a message box if the user tries activating the code without the selection being numeric data and can't quite get my head around it. If not numeric data, show message and exit sub. If numeric data, continue to process without a message.

Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If Not IsNumeric Then
        MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
        Exit Sub
        End If
        Else
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
        Loop Until F
End Sub

 

[Micron]

You're not providing an argument to the IsNumeric function - you have to pass something to it, same as how you passed an argument to IsEmpty. Not sure what that should be in your code - maybe
If Not IsNumeric(Cells(R,1))

 

[TheJay]

Thanks for your reply. I tried:

Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If Not IsNumeric(Cells(R, 1)) Then
        If Not IsNumeric(Cells(R, 2)) Then
        MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
        Exit Sub
        End If
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
    Loop Until F
End Sub

It didn't work and then says that there is a loop without do. Yet, the original code works fine without trying to implement the MsgBox.

 

[Micron]

Because Loop Until F is the start of another loop and the prior one has not be closed. You probably want Do Until F ?
If the condition being tested can change value inside the loop you probably need an If test within the loop. Without a comparison for F, True is assumed. I'm not seeing how F can ever be True ( or -1).
Correction - you have an un-terminated IF. By the time I would be able to edit to show how you should be indenting this post will be locked. Wait until I re-post your code.

 

[TheJay]

Thanks for your reply. I get the same error when I try:

Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If Not IsNumeric(Cells(R, 1)) Then
        If Not IsNumeric(Cells(R, 2)) Then
        MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
        Exit Sub
        End If
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
    Do Until F
End Sub

Here's a same of what is being checked.

The user highlights a cell, A18 for example. The code then looks at the trend and fills in the gaps in A19:B20. It doesn't matter how many row gaps there are.

 

[Micron]

Not sure if this is what will work for you but at least the structure should be correct

Sub AutoFill()
Dim R&, F%

R = ActiveCell.Row
Do Until F
   F = 1
   R = R + 1
   If Not IsNumeric(Cells(R, 1)) Then
      If Not IsNumeric(Cells(R, 2)) Then
         MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
         Exit Sub
      End If 'if you indent code blocks you shoud be able to easily spot a problem with the missing End If
   End If
   If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
   If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
Loop

End Sub

 

[TheJay]

Thanks Micron, that's closer to the end result I was hoping for. If it's not numeric, it's producing the MsgBox but it still attempts to fill in the gaps.

 

[TheJay]

Think I've got it.

Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
   If Not IsNumeric(Cells(R, 1)) Then
        If Not IsNumeric(Cells(R, 2)) Then
         MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
        Exit Sub
        End If
    End If
    Do Until F
        F = 1
        R = R + 1
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
    Loop
End Sub

 

[Micron]

Shouldn't your Exit sub be outside of the inner IF block or in both? IF first test is true (not numeric) the inner IF is evaluated. If True, it would exit, if false, control goes back to the outer IF which has no exit, thus the rest of the sub will run. Or should it be IF OR THEN or IF AND THEN rather than 2 interdependent conditions?

 

[TheJay]

Honestly not sure. All I know is that the MsgBox came up when it detected something other than numeric data and when clicking OK on the message box, it continued to fill the gaps anyway. When I moved the condition to the top, it brought up the message straight away if not numeric data and stopped processing.

On the two sheets I tested it on, it worked as expected on both. On contained numeric data and the other words and it filled the gaps correctly on the former and displayed the message on the latter with no further processing?