Place A Variable Value into A Column of Existing Data

[Ark68]

I have a variable (hgTime) with a time value of 7:15 PM.

I have column of time values (column F) for which I want to insert a blank cell at the point in the column that time (hgTime) would fit in between. So if this is my data ...

task allocation.xlsm
	F
13	4:30P
14	8:30A
15	8:30A
16	9:00A
17	9:00A
18	9:00A
19	1:00P
20	1:00P
21	4:30P
22	5:00P
23	5:00P
24	5:00P
25	6:00P
26	6:00P
27	6:00P
28	6:30P
29	7:00P
30	7:00P
31	7:00P
32	7:00P
33	9:00P
Master

Cells with Data Validation
Cell	Allow	Criteria
F13:F25	List	=nr_dsr2

The value would be placed between rows 32 and 33.

Would anyone be able to help find an efficient VBA solution to accomplish this. I was working with a series of loops, but that felt very awkward and I wasn't getting great results.

 

[johnnyL]

Couldn't you just append the value to the end of the column and then sort the column?

 

[Ark68]

Hmmm ... interesting approach JohnnyL. I might have to do a few additional steps as I may have simplified the situation in my OP. But that may do the trick.

 

[kevin9999]

I agree with @johnnyL that it would be simpler if you added the value to the end and then sort, however, if you're set on your proposed method, the following should give you what you want. I can't tell where your actual range starts in column F so I've assumed it's F13. Also, I've added the value of hgTime within the module - I assume you get it elsewhere?

Sub Insert_Time()
    Dim hgTime As Double, rng As Range, rNum As Long
    
    hgTime = 0.802083333333333      '<<~~ I assume you can get the actual value from somwhere
    Set rng = Range("F13", Cells(Rows.Count, "F").End(3))
    rNum = Application.Match(CDbl(hgTime), rng, 1) + 1
    
    With rng.Cells(rNum, 1)
        .Insert 2
        .Offset(-1).Value = hgTime
    End With
End Sub

Before

header.xlsb
	F
13	4:30P
14	8:30A
15	8:30A
16	9:00A
17	9:00A
18	9:00A
19	1:00P
20	1:00P
21	4:30P
22	5:00P
23	5:00P
24	5:00P
25	6:00P
26	6:00P
27	6:00P
28	6:30P
29	7:00P
30	7:00P
31	7:00P
32	7:00P
33	9:00P
Sheet2

After

header.xlsb
	F
13	4:30P
14	8:30A
15	8:30A
16	9:00A
17	9:00A
18	9:00A
19	1:00P
20	1:00P
21	4:30P
22	5:00P
23	5:00P
24	5:00P
25	6:00P
26	6:00P
27	6:00P
28	6:30P
29	7:00P
30	7:00P
31	7:00P
32	7:00P
33	7:15P
34	9:00P
Sheet2

 

[johnnyL]

Question for you @kevin9999. Why couldn't the 7:15P be inserted between row 13 & row 14?

 

[kevin9999]

Question for you @kevin9999. Why couldn't the 7:15P be inserted between row 13 & row 14?

I think it's to do with the Match argument 1 which (according to the Microsoft documentation) finds the largest value that is less than or equal to the lookup value. Therefore, 7:00pm is larger than 4:30pm. Don't ask me why it doesn't choose row 29

 

[Ark68]

Hi Kevin, thank you for sharing your suggestion, I do appreciate it.
I have tried to adapt it to my situation. My OP was really just conceptual and didn't fully portray the full picture of what's needed.

First off, being a novice to VBA, may I ask what the (3) in this line provides? I've seen this in a few support answers.

Set rng = Range("F13", Cells(Rows.Count, "F").End(3))

Similarly, the value of 2 in this line?

.Insert 2

Now, to my unique need that may or may not affect whether this method will still be appropriate. I'm not getting any errors, but I'm defiantly not getting the results I need.

Your solution results in one cell being inserted in column F to accept the value of hgTime, which it does do. For my purposes, I need to insert a range of cells at the appropriate row. If the target row is 21, instead of just inserting a cell and putting the value in at F21, I need to insert cells A21:Q21. With that opened up, with "source range" A#:Q# where # is a pre-calculated row number represented by variable 'srvc_drow - 1', is copied and pasted to A21. So yeah, a bit more of an expectation than what was in my OP.

I tried experimenting a bit to see if I could figure out how the code works, but some things appeared a bit out of my league and I'm afraid I was unable to detect where I needed to make adaptations.

My code, rather than a separate procedure is within a module of existing code.

  With ws_master
        . . . 
        svc_time = .Cells(srvc_drow - 1, 8).Value
        Set rng = .Range("F13:F" & srvc_drow - 1)
        rNum = Application.Match(CDbl(svc_time), rng, 1) + 1
        With rng.Cells(rNum, 1)
             .Insert 2
             .Offset(-1).Value = svc_time
        End With
        . . . 
  End With

BTW ... the 4:30 PM at the top of column F in my sample data was an error a result of a poor sort. It would have been in proper sequence with the rest of times had the sort been correct.

 

[kevin9999]

I like to use shorthand in code, purely for aesthetic reasons (looks neater). The first (3) is the equivalent enumeration of xlUp. The 2 following .Insert is the equivalent enumeration of xlDown.

With regard to inserting cells from A to Q, you should achieve the insert (assuming column F is still the column with the times listed) by changing (in my code and untested)

With rng.Cells(rNum, 1)

to

With rng.Cells(rNum, 1).Offset(, -5).Resize(1, 17)
    .Offset(-1).Value = svc_time

 

[Ark68]

Thank you Kevin, that did indeed insert the cells across the range of A:G.
I need to now copy the range to that newly created inserted row, but how do I get that row number? rNum, in my case = 21, but the row was inserted at 34. Row 34 is actually the row I need to copy cells from. So I need to copy range A34:Q34 and paste to A?:Q? where ? is the newly inserted row.

 

[kevin9999]

I'm away from my laptop for a few hours, will get back to this later.