• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need code that can detect empty rows and select the two neighbouring cells with data in

This doesn't work

Code: 
Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If IsNumeric IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsNumeric IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
        Else: MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
        Loop Until F
End Sub
 
As you must see in VBA help that should be If {condition1} And (Condition2) Then {true part} Else Msgbox {message} ...​
​
 
I'm still not sure because the original code has if and then statements on the same lines.

Code: 
Sub AutoFill()
    Dim R&, F%
        R = ActiveCell.Row
    Do
        F = 1
        R = R + 1
        If IsNumeric Then
        Else: Exit Sub
        If IsEmpty(Cells(R, 1)) Then F = 0: Cells(R, 1) = Cells(R - 1, 1)
        If IsNumeric And IsEmpty(Cells(R, 2)) Then F = 0: Cells(R, 2) = Cells(R - 1, 2) + 1
        Else: MsgBox ("The selection is text, this keyboard shortcut will only work with numbers."), vbOKOnly + vbInformation, "Shortcut Information"
        Loop Until F
End Sub
 
As you can easily switch on multi lines if you prefer :​
​
If {condition1} And (Condition2) Then​
{true part}​
Else​
Msgbox {message}​
End If​
​
 
If I want to test for IsNumeric and if not, exit sub, how do I need to change what I have proposed above? I'm not sure.
 
This is the closest I can get, still not valid:

Code: 
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
 
Well, see post #33 as you forgot the brackets and what to check or just reading VBA help, try, at very beginner level ...​
​
 
Code: 
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
 
Code: 
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
 
You must log in or register to reply here.
Back
Top