Need help on VBA

[Heet6498]

Hi, I am trying to automate one thing using VBA. So, I want to place 1s based on the provided starting point and end point. So for example, on 1st row I have times cell C1 to so on.( 8am, 9am, 10am,......23pm) and now if I put start time 10AM in A2 and end time in different cell 9PM in B2. Then I want to place 1s in C2 to So one based on the references from 1st row and based on my starting and end point. Thanks for the help in advance.

[Aussiebear]

Welcome to VBAX Heet6498. Sorry but we are going to need a sample workbook to understand the logic of your issue. Are you able to attach one please? Click on Go advanced/ Manage attachments/Add Files/ChooseFile/Upload/Submit reply.

[Heet6498]

Hi, I have attached one file. In columns A and B, I have start times and end times. On 1st row, I have times with the increment of 1hr. Now If i want to create a VBA tool, which can go to each starting time and end time and put 1s in the range.

[arnelgp]

here you try this


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, dt1 As Date, dt2 As Date
Dim j As Integer, k As Integer
Application.EnableEvents = False
If Left$(Target.Address, 2) = "$B" Then
If InStr(1, Target.Address, ":") = 0 Then
If Val(Range("$A$" & Target.Row)) <> 0 And Val(Target) <> 0 Then
dt1 = Range("$A$" & Target.Row).Value
dt2 = Target.Value
i = DateDiff("h", dt1, dt2)
j = DateDiff("h", #12:00:00 AM#, dt1) + 4
For k = j To j + i
Cells(Target.Row, k) = 1
Next
End If
End If
End If
Application.EnableEvents = True
End Sub

[Heet6498]

Hi, as I click on the run button, its asking me to create a new macro. Is there something I am missing? Sorry, I am supper new to the VBA stuff.

[arnelgp]

don't worry about the macro, it will run automatically when you fill the "Start Time" and "End Time" on the worksheet.
or if you already have them inputed, re-input the "End Time" so that the macro get fired-up.

[Heet6498]

31390
This is what i get after hitting the RUN button on VBA. after re-entering the start time and end time. I have attached one image.

[arnelgp]

to make it easier for you, i created a button on the worksheet.
press it and your time calendar will be filled with 1's.

[Heet6498]

: pray2: Wizard Stuff Right There!!... Thank you so much for helping me out with this. Can you also recommend me some learning resources for VBA? YouTube have plenty but there is no rode map or some kind of step by step structure. If you can recommend me some then it will be super awesome.

[Heet6498]

One More question, In this example we have interval by 1HR, what if on the first row we have interval by 15Min. for example (1pm, 1:15pm,1:30pm....) and start time and end time can also have times like 3:45PM to 9:30PM.

[Aussiebear]

: pray2: Wizard Stuff Right There!!... Thank you so much for helping me out with this. Can you also recommend me some learning resources for VBA? YouTube have plenty but there is no rode map or some kind of step by step structure. If you can recommend me some then it will be super awesome.

Some styles don't suit everyone, but i enjoy Mynda's efforts at MyOnlineTrainingHub.com

[arnelgp]

sheet2 has interval of 15 minutes.
for you to test.

[p45cal]

A formula in a single cell can do this too. For the 15 minute intervals, in cell D3:
=IF((D$1:CV$1>=$A3:$A5)*(ROUND((D$1:CV$1+1/96),8)<=ROUND($B3:$B5,8)),1,"") which will spill into rows and columns, and should update itself when times change in columns A & B.

[Heet6498]

LoL, why didn't I think of this before. You made this task look so easy. Thanks for this recommendation.