Loop or for/next?

[Homegrownandy]

Ive put the code I want to use in a loop and I get subscript out of range:

Sub datasort()
Dim celltxt As String
Dim celltxt2 As String
 

'copy name
Range("r1").Select
Selection.Copy
Range("i2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'copy date
Range("c2").Select
Selection.Copy
Range("j2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'copy PO
Range("d2").Select
Selection.Copy
Range("k2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'copy hours
Range("f2").Select
Selection.Copy
Range("l2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 Range("A2:F2").Select
    Application.CutCopyMode = False
    Selection.ListObject.ListRows(1).Delete
    
'copy miles
Range("f2").Select
Selection.Copy
Range("m2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 Range("A2:F2").Select
    Application.CutCopyMode = False
    Selection.ListObject.ListRows(1).Delete
    
'validate expenses'''''''''''
 
 
celltxt2 = ActiveSheet.Range("e2").Text
    celltxt = ActiveSheet.Range("s1").Text
    
    
If InStr(1, celltxt2, "Exp") Then
    If InStr(1, celltxt, "TRUE") > 0 Then
  
    Range("A2:F2").Select
    Application.CutCopyMode = False
    Selection.ListObject.ListRows(1).Delete
    Else
    MsgBox ("Expenses")
    Exit Sub
    End If
''''''''''''''''''''''''''''
Else
MsgBox "raw data is formatted incorrectly"
Exit Sub
Workbook.Save
 
 
 
End If
 
 
 

'insert new line
  Range("I2:M2").Select
   
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    '''''''''''loop before here''''''''''''''''''''


    '''''email''''
     Dim rng As Range
 rng = "test@test.co.uk"
 ActiveWorkbook.SendMail Recipients:=rng, Subject:="The report you need"
 'Add a button to the sheet
Exit Sub
    
    
End Sub

Everything that happens before the annotation " loop before here" is what I want to loop. If cell B2 is not null then....

I have deleted the loop when I was trying to use the for and next function but that was even less successful.

The loop DID work until the error, then it did not pass the loop to the email.

I'm hoping someone can help. thanks.

[Homegrownandy]

 Do While Not IsEmpty(Range("b2"))

CODE

loop

Is the code I am/was using.. a different error now. Error 91. object variable or with block variable not set.

this happens where B2 is null

[Homegrownandy]

It was the new variable rng causing the issue. Seems okay now. I was defining it as a range and entering just text. That's what I get