how to insert 5 blank rows based on column E

July 4, 2018 at 03:13:36
Specs: Windows 7
how to insert 5 blank rows based on column E (Date) value but user need to enter date in input box.

For example: E row has date range from 01.01.2018 to 31.01.2018 if I enter the date >= 10.01.2018 then insert 5 rows between 9 to 10.


See More: how to insert 5 blank rows based on column E

Reply ↓  Report •

#1
July 4, 2018 at 05:11:30
Please? Thank you? Appreciate your help?

message edited by DerbyDad03


Reply ↓  Report •

#2
July 4, 2018 at 23:30:31
Please help me to fix it.

Reply ↓  Report •

#3
July 5, 2018 at 07:02:36
When you say "E row has date range" I assume you mean Column E. If that is correct, try this code:

Public Sub UserDate()
  Dim strDate As String
  Dim d As Range
  
'Get Date From User
GetDate:
  strDate = Application.InputBox _
             ("Insert date in format dd.mm.yyyy" _
             & vbCrLf & vbCrLf _
             & "Click Cancel to Exit", _
             "User date", Format(Now(), "dd.mm.yyyy"))
             
'Handle Cancel Button
     If strDate = "False" Then Exit Sub
     
'Handle Improper Date Format
     If IsDate(strDate) Then
       strDate = Format(CDate(strDate), "dd.mm.yyyy")
     Else
       MsgBox "Incorrect date format" _
         & vbCrLf & vbCrLf _
         & "Insert date in format dd.mm.yyyy"
       GoTo GetDate
     End If
     
'Find Date And Insert Rows
     With Columns(5)
      Set d = .Find(CDate(strDate))
        If Not d Is Nothing Then
           Range(Cells(d.Row, 5), Cells(d.Row + 4, 5)).Insert shift:=xlDown
        Else
        
'Handle Date Not Found
            MsgBox "Date Not Found" _
              & vbCrLf & vbCrLf _
              & "Please Try Again"
          GoTo GetDate
        End If
     End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
July 6, 2018 at 03:27:16
Hi,

Thanks for you kind help, I have one small correction, the current code is like if there is particular date the rows will insert, but
I will show you the example: 01.01.2018 and the next row is 04.01.2018 but I have cutoff date is 02.01.2018, but it is not there in column E, if I enter the Date as 02.01.2018 it should not show the "Please try again", I need to insert rows between the 01.01.2018 and 04.01.2018.

Please help me with this task.


Reply ↓  Report •

#5
July 6, 2018 at 07:22:57
Before I answer your latest question, please allow me to offer a posting tip. I hope that you will accept this tip in the spirit that it is intended, which is to let you know how you can make it easier for people to help you.

Please keep in mind that we cannot see your workbook from where we are sitting nor do we know anything about your work processes. The only thing we have to work with is what you tell us in your posts. When working on a solution, we often have to set up test worksheets using the data layout that you describe in your posts. In this example, when you tell us that "Column E has date range from 01.01.2018 to 31.01.2018" and that you want rows inserted when a specific date is entered by a user, that's all we know, so that is all we have to work with. We spend our time coming up with a solution to meet those requirements. When you come back later with additional (or different) requirements, we have to rework our solution, set up the test sheets again and sometimes even start from scratch. Please try to be as specific and as detailed as possible when you first post your requirements.

That said...

I want to make sure that I understand your latest requirements before I proceed.

In the example given in Response # 4, it appears that you want to start with this:

              E
1      01.01.2018
2      04.01.2018 

and end up with this, after the user enters 02.01.2018:

              E
1      01.01.2018
2
3
4
5
6
7      04.01.2018 

Is there anything else that you think we need to know or deal with?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
July 9, 2018 at 05:13:17
Yes, your example is my requirement, Kindly help, Henceforth I will post the requirements with full details.

Thank you so much.


Reply ↓  Report •

#7
July 18, 2018 at 21:49:36
Hi, Is there any update regarding my followup, kindly do the needful,

Thanks in advance!


Reply ↓  Report •

#8
July 19, 2018 at 07:11:51
I'm not sure why you marked the thread as Solved if you haven't received a working solution yet. I have reset the Best Answer choice.

Try the following code. Note: I see that you are using a Date format of dd.mm.yyyy. My system uses mm/dd/yyyy and I do not want to change my Regional settings or default Date format just to test the code. It messes up my real work.

This should work, but obviously I can't test it under your operating environment, so you'll have to let me know. If it doesn't work, please don't come back and just say "It doesn't work". That will not help me figure out what is wrong. Tell me exactly what happens (or doesn't happen) including any error messages that you get.

Sub InsertByDate()

'Macro To Insert Rows Based On User Date

Dim datein As Date, lastRw As Long, d As Range
On Error Resume Next

'Get Date From User
GetDate:
    datein = Application.InputBox _
             ("Insert date in format dd.mm.yyyy" _
             & vbCrLf & vbCrLf _
             & "Click Cancel to Exit", _
             "User date", Format(Now(), "dd.mm.yyyy"))
             
'Handle Improper Date Format
      If Err.Number = 13 Then
       On Error GoTo -1
        MsgBox "Incorrect date format" _
               & vbCrLf & vbCrLf _
               & "Insert date in format dd.mm.yyyy"
          GoTo GetDate
      End If
         
'Handle Cancel Button
     If datein = False Then Exit Sub
      
'Insert 5 Rows Above Next Largest Date
    lastRw = Cells(Rows.Count, 5).End(xlUp).Row

       For Each d In Range("E1:E" & lastRw)
         If d.Value > datein Then
             Range(Cells(d.Row, 5), Cells(d.Row + 4, 5)).EntireRow.Insert shift:=xlDown
             Exit For
         End If
       Next

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#9
July 19, 2018 at 07:20:18
Hi,

Super, It is working fine, Thank You So Much for such a great help.


Reply ↓  Report •

#10
August 2, 2018 at 05:23:17
Hi,

Thanks for your help

I got an new development idea in this macro

Need to run this macro for all sheets except one sheet at one time with single date input.

For Example:

In my workbook I have 6 sheets named as A, B, C, D, E, F

If I run the code, input box will appear and I will give input date as 10.06.2018

then it should insert rows for all the applicable worksheets except worksheet name A

Thanks in advance for valuable code.


Reply ↓  Report •

#11
August 2, 2018 at 12:37:32
This is untested because I don't feel like setting up a workbook to run it against. Let me know if it throws up any errors. Note the section after the asterisks.

Public Sub UserDate()
  Dim strDate As String
  Dim d As Range
  
'Get Date From User
GetDate:
  strDate = Application.InputBox _
             ("Insert date in format dd.mm.yyyy" _
             & vbCrLf & vbCrLf _
             & "Click Cancel to Exit", _
             "User date", Format(Now(), "dd.mm.yyyy"))
             
'Handle Cancel Button
     If strDate = "False" Then Exit Sub
     
'Handle Improper Date Format
     If IsDate(strDate) Then
       strDate = Format(CDate(strDate), "dd.mm.yyyy")
     Else
       MsgBox "Incorrect date format" _
         & vbCrLf & vbCrLf _
         & "Insert date in format dd.mm.yyyy"
       GoTo GetDate
     End If
     
'*********
'Loop through sheets, ignore Sheet A
 For nxtSht = 1 To Sheets.Count
  If Sheets(nxtSht).Name <> "A" Then
  
'Find Date And Insert Rows
   With Sheets(nxtSht)
     With .Columns(5)
      Set d = .Find(CDate(strDate))
        If Not d Is Nothing Then
           .Range(.Cells(d.Row, 5), .Cells(d.Row + 4, 5)).Insert shift:=xlDown
        Else
        
'Handle Date Not Found
            MsgBox "Date Not Found" _
              & vbCrLf & vbCrLf _
              & "Please Try Again"
          GoTo GetDate
        End If
     End With
   End With
  End If
 Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question