Type mismatch error msg - search thru a column

[GS7CLW]

Hope everyone had a great weekend!!!

It's Monday:>(( and I am

trying to loop thru the cells in Col F and check for a number in the cell.
IF is is a number, check the next cell down to see if it is blank.
IF it is NOT blank insert a row; ELSE continue on.

Getting Type Mismatch error using this code:

Sub Insert_a_Row_After_Number()
Application.ScreenUpdating = True
    Dim Rng
    '-----go thru the cells (F2 - F75) and select those with a number in the cell------
    Set Rng = Range("F2:F350")
    For Each c In Rng
        '----------if the cell contains a number then check next cell down for a empty cell ------------
        If IsNumeric(c) And c.Value <> "" And c.Value + 1 <> "" Then
        ' ----------- if cell is empty move on to the next cell------------
            GoTo NextCell
    Else
    ' ---------- if the cell is not empty - insert a blank row -------------
    Range("F" & c.Value + 1).Select
    Selection.EntireRow.Insert
         End If
NextCell:
Next c
Application.ScreenUpdating = True
End Sub

 

[Tinbendr]

You can't add one to C. You have to use offset.

If IsNumeric(c) And c.Value <> "" And c.Offset(1 ,0).Value <> "" Then

But I couldn't get yours out of a endless loop.

Try this

Sub Test1()
Dim WS As Worksheet
Dim A As Long
For A = 25 To 2 Step -1
If IsNumeric(Range("F" & A)) And Range("F" & A).Value > 0 Then
    If Range("F" & A + 1).Value <> "" Then
        Range("F" & A + 1).EntireRow.Insert
    End If
End If
Next
End Sub

 

[GS7CLW]

TODAY is definitely MONDAY...

for some reason, I am still getting a type mismatch:

Sub Insert_a_Row_After_Number()
Application.ScreenUpdating = True
Dim WS As Worksheet
Dim A As Long
For A = 25 To 2 Step -1
If IsNumeric(Range("F" & A)) And Range("F" & A).Value > 0 Then
If Range("F" & A + 1).Value <> "" Then
Range("F" & A + 1).EntireRow.Insert
End If
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Tinbendr]

Could you post a sampling of the data? 20 row should be good enough.

 

[GS7CLW]

<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=157><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 118pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15 width=157>COLLIER, ZACHARY T</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>ANDERSON, JORDAN C</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>HUGHES, ANDREW J</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: yellow; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=15>3</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>MCCLAIN, ALEXANDER D</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>ROBINSON, ADAM R</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>DEVOTO, KYLE S</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: yellow; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=15>3</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>GODDARD, TROY D</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>GALLION, MATTHEW S</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>SHAW, CARI L</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>FLOYD, DARIN V</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: yellow; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=15>4</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>COFFEY, JOSEPH R</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>COOPER, ETHAN W</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>BOZEMAN, BRANDON W</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: yellow; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=15>3</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>LAFON, JEREMY M</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>LOPEZ, DARIAN C</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>STEELE, CARL E</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>FLUM, MATTHEW J</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>HENRY, TAYLOR E</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>KELLEY, JOHN M</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>ANDERSON, NATHAN R</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>LEGLEU, COURTNEY N</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>CHAMBERLAIN, KATIE A</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: yellow; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=15>9</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63 height=15>SAYRE, JOSHUA D</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63 height=15>DOWNING, JOSHUA W</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: yellow; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=15>2</TD></TR></TBODY></TABLE>

when done I need a blank row after each cell with a number.
some will have a blank row already some will not.

 

[Tinbendr]

I don't know. The last code you posted ran without incedent.

Here's a refined version.

Sub Insert_a_Row_After_Number()
Application.ScreenUpdating = True
Dim WS As Worksheet
Dim A As Long
Dim LastRow As Long
Set WS = ActiveSheet
Application.ScreenUpdating = False
With WS
    LastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
 
For A = LastRow To 2 Step -1
    If IsNumeric(.Range("F" & A)) And .Range("F" & A).Value > 0 Then
        If .Range("F" & A + 1).Value <> "" Then
            .Range("F" & A + 1).EntireRow.Insert
        End If
    End If
Next
End With
Application.ScreenUpdating = True
End Sub

 

[GS7CLW]

Re: Type mismatch error msg - search thru a column - SOLVED

just added a couple lines to clear the yellow fill.

Works PERFECTLY!!!

I appreciate your help.
I also, appreciate you "sticking with me"!!

Have a great week!

cliff