Using an If statement, produces runtime-13 type mismatch error

Private Sub CommandButton1_Click() ''Dim rCell As Range
Dim i As Long
Dim rNext As Range

'loop through the cells in column A of the source sheet
For Each rCell In Sheet4.Range("A3:U25"开发者_如何转开发)
'loop as many times as the value in column U of the source sheet
    For i = 1 To rCell.Offset(0, 23).Value
        'find the next empty cell to write to in the dest sheet
        Set rNext = Sheet12.Cells(Sheet12.Rows.Count, 1).End(xlUp).Offset(1, 0)
        'copy A and B from source to the dest sheet
        rCell.Resize(1, 23).Copy
        rNext.Resize(1, 1).PasteSpecial (xlPasteValues)
    Next i
Next rCell
End Sub

1. I'm getting a runtime-13 type mismatch at line For i = 1 To rCell.Offset(0, 23).Value . When it errors, I click end and it works fine. Don't want to have to click end.
2. In my form I'm using an If statement =IF(E4>0,1,"") to produce the 1 needed. If I remove the If statement and manually put the 1 in place then it works without errors.

The error occurs because the ending range in your FOR LOOP is not a type of LONG.

If I'm reading your second bullet point correctly, I think your IF statement is off. Instead of =IF(E4>0,1,""), the FALSE criteria of the IF should be numeric (e.g. =IF(E4>0,1,2).

You can verify by putting the following statement above the problem FOR LOOP:

Debug.Assert IsNumeric(rCell.Offset(0, 23).Value)

This will stop the code from executing and you can put this in the immediate window to see what the value is and the location of the cell in question:

? rCell.Offset(0, 23).Value
? rCell.Offset(0, 23).Column
? rCell.Offset(0, 23).Row

Ok I fixed the problem:

=IF(E4>0,1,"") needs to be =IF(E4>0,1,0)

Cannot look for a blank, has to be a 1 or 0. or numeric value as you said. Simple enough. Thanks for the help. Didn't even see that.