MsgBox Error

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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.
1595988608802.png



The only difference is the MsgBox goes into capitals
1595988719903.png


1595988788418.png


any ideas why this is happening?

Cheers
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Works perfect, thank you!

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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows
copy and paste what ??
and from where?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows
Do you mean
VBA Code:
cells(r,4).Copy cells(r+2,4)
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Like this ??
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You mean run until the line is found then exit ??
Try
VBA Code:
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

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I want it to find the the row, insert the new row then finish
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

The code in Post #25 does that !
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hmm, it gives me an error with the End If "End if without block if".
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows
This the full code from post #25....works fine for me !
VBA Code:
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

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,943
Messages
5,525,771
Members
409,663
Latest member
littleriver

This Week's Hot Topics

Top