Theva
asked on
Convert formula into macro
Hi Experts,
I would like to request Experts help to create a macro for converting local time into GMT at column AB and AC at Time sheet based on this formula:
=IF(Z13="","",24+Z13-TIME( 8,0,0))
The reason for macro is sometime user accidently deleted the formula at the cell and that create big mess. Hope Experts can help me to create this feature. I have attached the workbook for Experts perusal.
Time.xls
I would like to request Experts help to create a macro for converting local time into GMT at column AB and AC at Time sheet based on this formula:
=IF(Z13="","",24+Z13-TIME(
The reason for macro is sometime user accidently deleted the formula at the cell and that create big mess. Hope Experts can help me to create this feature. I have attached the workbook for Experts perusal.
Time.xls
ASKER
Hi Roberto,
I need your help. Currently I'm having the same "Private Sub Worksheet_SelectionChange( ByVal Target As Range)" in my actual workbook. How to combine your solution with the attached script. Hope you will consider this request.
I need your help. Currently I'm having the same "Private Sub Worksheet_SelectionChange(
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errHandler
Application.EnableEvents = False
If Target.Count > 1 Then GoTo finished
If Target.Column = 4 And Target.Row >= 13 And Target.Row <= 37 Then
UserForm1.Show
On Error Resume Next
Target.Value = CDate(formDate) & " " & CDate(formTime)
On Error GoTo 0
Else
End If
finished:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description
Resume finished
End Sub
Theva,
I'm not sure, but you could try this:
Roberto.
Roberto.
I'm not sure, but you could try this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errHandler
Application.EnableEvents = False
If Target.Count > 1 Then GoTo finished
If Target.Column = 4 And Target.Row >= 13 And Target.Row <= 37 Then
UserForm1.Show
On Error Resume Next
Target.Value = CDate(formDate) & " " & CDate(formTime)
Range("AB" & Target.Row).FormulaR1C1 = "=IF(RC[-2]="""","""",24+RC[-2]-TIME(8,0,0))"
Range("AC" & Target.Row).FormulaR1C1 = "=IF(RC[-2]="""","""",24+RC[-2]-TIME(8,0,0))"
On Error GoTo 0
Else
End If
finished:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description
Resume finished
End Sub
Roberto.
Roberto.
ASKER
Hi,
Shows ambiguous error. Is that possible to run this procedure from “Private Sub Workbook”?
Shows ambiguous error. Is that possible to run this procedure from “Private Sub Workbook”?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Roberto,
Thanks for the help.
Thanks for the help.
Attached is your file with a code that will add the formula you need when doing a change in any cell in the range of your template (columns Z and AA)
Hope it helps.
Roberto.
Time-1-.xls