VBA: Insert date in between 2 dates

JennV

New Member
Joined
May 9, 2019
Messages
34
Sorry, I couldn't think of a better title...

2019-01-096.5
2019-01-067
2019-01-078
2019-01-089.9
2019-01-104.3
2019-01-115.5

<tbody>
</tbody>

I have a set of data from 2019-01-06 to 2019-01-11. Let's say I create a userform so that the user can input a date (2019-01-09) and a value (6.5). Is there a code that can insert this information accordingly within the data? (below 2019-01-08 and above 2019-01-10 along with the value)

In addition, I'm not sure if there is a fill button on here but let's say the values (7, 8, 9.9, 4.3, 5.5) are filled in with yellow and the value entered (6.5) is supposed to be purple because it's a different event, is there a way to incorporate this into the code?

Thank you in advance!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
This code makes some assumptions.
  • Your data is in columns A and B.
  • You have header columns.
  • The textboxes on your userform and named tbDate and tbVal.
  • The code below will be part of a command button.
  • You've already colored the original data yellow.

I tested it on the sample data you gave and it seems to work as intended.

Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row + 1
Dim r As Range: Set r = Range("A1:B" & LR)

With Range("A" & LR & ":B" & LR)
    .Value = Array(CDate(Me.tbDate), Val(Me.tbVal))
    .Interior.ColorIndex = 7
End With

r.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
This code makes some assumptions.
  • Your data is in columns A and B.
  • You have header columns.
  • The textboxes on your userform and named tbDate and tbVal.
  • The code below will be part of a command button.
  • You've already colored the original data yellow.

I tested it on the sample data you gave and it seems to work as intended.

Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row + 1
Dim r As Range: Set r = Range("A1:B" & LR)

With Range("A" & LR & ":B" & LR)
    .Value = Array(CDate(Me.tbDate), Val(Me.tbVal))
    .Interior.ColorIndex = 7
End With

r.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Application.ScreenUpdating = True
End Sub

This is amazing, thank you so much!

I have a follow-up question, if you don't mind. Is it possible to get the value of tbDate in another cell as well? For example, after the user enters the data, it does the above and puts the date in another cell (say, D1). Would we use something like 'selection.copy'? (Sorry, I have very limited VBA experience).
 
Upvote 0
Just add a line after the ‘End With’ that says ‘Range(D1).Value = Me.tbDate’. Also, I avoid copy and paste in VBA like the plague. It’s slow and clunky. Since you say you’re a VBA novice. The macro recorder is a good teaching tool. But also avoid selecting cells. If you want to copy a value to another place, just write range(d1).value = range(a1).value or whatever.
 
Upvote 0
Just add a line after the ‘End With’ that says ‘Range(D1).Value = Me.tbDate’. Also, I avoid copy and paste in VBA like the plague. It’s slow and clunky. Since you say you’re a VBA novice. The macro recorder is a good teaching tool. But also avoid selecting cells. If you want to copy a value to another place, just write range(d1).value = range(a1).value or whatever.

Again, thank you so much!!! I really appreciate the tip as well, cheers :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top