MsgBox Error

[jreed5535]

Hi Guys,

I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2.

The only difference is the MsgBox goes into capitals

any ideas why this is happening?

Cheers

 

[jreed5535]

Works perfect, thank you!

Now how would i copy and pate into that new inserted row?

 

[Michael M]

copy and paste what ??
and from where?

 

[Michael M]

Do you mean

cells(r,4).Copy cells(r+2,4)

 

[jreed5535]

Like this ??

Sub MM1()
Dim r As Long, ws As Long
Set ws = Sheets("Sheet1")
For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
If Cells(r, 4).Value = ws.Cells(2, 4).Value Then   Cells(r + 2, 4).EntireRow.Insert
Next r
End Sub

How do i only get this to run once? instead of Next

 

[Michael M]

You mean run until the line is found then exit ??
Try

For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
If Cells(r, 4).Value = ws.Cells(2, 4).Value Then 
Cells(r + 2, 4).EntireRow.Insert
exit For
end if
Next r

 

[jreed5535]

I want it to find the the row, insert the new row then finish

 

[Michael M]

The code in Post #25 does that !

 

[jreed5535]

Hmm, it gives me an error with the End If "End if without block if".

 

[Michael M]

This the full code from post #25....works fine for me !

Sub MM1()
Dim r As Long, ws As Worksheet
Set ws = Sheets("Sheet1")
For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
If Cells(r, 4).Value = ws.Cells(2, 4).Value Then
Cells(r + 2, 4).EntireRow.Insert
Exit For
End If
Next r
End Sub

 

[jreed5535]

Not sure what happened, working for me now too!

Have the file working perfectly now, thank you very much for all your help Michael!

Greatly appreciated!