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 •

Ask Question