Macro that will find all times and add this formula

**g1terra** · 08-26-2017, 02:44 AM

Hello All,

I am trying to crate a macro that will find the times on this sheet and add this formula in the yellow highlighted cells. =C15+TIME(6,30,0)

The issue it it will be used on multiple time cards like this but they will not all be the exact same. The cards will all look like this but the times may not be on the same rows (they will be on the same columns though). In other words this card may populate more shift times and some fewer.

I am attaching an example and the yellow highlights is where i need the formula to populate. As you can see in red font i did the first one. What i need this macro to find is the (start time plus 6 hrs and 30 minutes) in all highlighted areas.

Any ideas will be greatly appreciated!

**sintek** · 08-26-2017, 04:01 AM

Hi g1terra

they will be on the same columns though

Are you saying that the formula will only have to be entered in columns E and J

If so then this should work...
Have commented out loop through worksheets...You mentioned there might be more time cards...

Option Explicit

Sub FormulaInsert()
Dim ws As Worksheet
Dim Cell As Range
Application.ScreenUpdating = False
'For Each ws In ThisWorkbook.Sheets
    For Each Cell In ActiveSheet.Range("E:E,J:J")
        If Not IsEmpty(Cell.Offset(, -1).Value) And IsDate(Cell.Offset(, -1).Text) Then
            Cell.Value = Cell.Offset(, -2).Value + TimeSerial(6, 30, 0)
            Cell.NumberFormat = "hh:mm:ss"
        End If
    Next Cell
'Next ws
Application.ScreenUpdating = True
End Sub

**g1terra** · 08-28-2017, 02:04 PM

Sorry I am having computer issues for some in San Juan curently, I did reply to this and for some reason it did not post. Thank you so much i actually called you a genius. Not good internet here..

**sintek** · 08-28-2017, 02:24 PM

Coolio...
For others...See amended code in this post....
https://www.excelforum.com/excel-pro...un-faster.html

Macro that will find all times and add this formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro that will find all times and add this formula

Re: Macro that will find all times and add this formula

Re: Macro that will find all times and add this formula

Re: Macro that will find all times and add this formula

Thread Information

Users Browsing this Thread

Similar Threads

Multi-Cristeria Formula using LEFT and FIND and DATES and TIMES to refine criteria

Formula to find call times over 70 seconds for a given date from CSV

[SOLVED] Macro for Text Search to Find Same Word Multiple Times

[SOLVED] Formula to find the number of times a row has the max value of the column

Need a Formula Find sections in a column and return start and end times

Macro Loop – Find Specific Times, Extract Date, Average Values of Times

Macro to help sort date - times and average similar times.

Bookmarks

Bookmarks

Posting Permissions