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.
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
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
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.
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.
Capture.jpg
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.
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.
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.
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.
Some styles don't suit everyone, but i enjoy Mynda's efforts at MyOnlineTrainingHub.comOriginally Posted by Heet6498
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
sheet2 has interval of 15 minutes.
for you to test.
A formula in a single cell can do this too. For the 15 minute intervals, in cell D3:which will spill into rows and columns, and should update itself when times change in columns A & B.=IF((D$1:CV$1>=$A3:$A5)*(ROUND((D$1:CV$1+1/96),8)<=ROUND($B3:$B5,8)),1,"")
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
LoL, why didn't I think of this before. You made this task look so easy. Thanks for this recommendation.
Posting Permissions
Reply With Quote
