Hello humacdeep,
The macro below has been added to a button on Sheet1. This will fill the table with the formulae to display the times.
' Thread: http://www.excelforum.com/excel-programming/793159-macro-for-converting-time-zone.html
' Poster: humacdeep
' Written: September 20, 2011
' Author: Leith Ross
Sub UpdateTimeTable()
Dim Cell As Range
Dim Rng As Range
Dim Wks As Worksheet
Set TimeTable = Worksheets("Sheet1").Range("C1").CurrentRegion.Offset(1, 0)
Set TimeTable = TimeTable.Resize(RowSize:=TimeTable.Rows.Count - 1)
TimeTable.ClearContents
TimeTable.Cells(1, 1).Formula = "=IF(Sheet2!C2-TIME(10,30,0 )>0,Sheet2!C2-TIME(10,30,0 ),"""")"
TimeTable.Cells(1, 2).Formula = "=IF(Sheet2!D2-TIME(10,30,0 )>0,Sheet2!D2-TIME(10,30,0 ),"""")"
TimeTable.Cells(1, 3).Formula = "=IF(Sheet2!E2-TIME(10,30,0 )>0,Sheet2!E2-TIME(10,30,0 ),"""")"
TimeTable.Rows(1).Cells.AutoFill TimeTable.Rows.Cells
End Sub
Bookmarks