VBA: Insert date in between 2 dates

[JennV]

Sorry, I couldn't think of a better title...

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]2019-01-09[/TD]
[TD]6.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019-01-06[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2019-01-07[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2019-01-08[/TD]
[TD]9.9[/TD]
[/TR]
[TR]
[TD]2019-01-10[/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]2019-01-11[/TD]
[TD]5.5[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[lrobbo314]

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.

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

 

[JennV]

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.

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).

 

[lrobbo314]

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.

 

[JennV]

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