Link to home
Start Free TrialLog in
Avatar of Theva
ThevaFlag for Malaysia

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
Avatar of Pabilio
Pabilio
Flag of Spain image

Hi Theva...

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
Avatar of Theva

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.
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

Open in new window

Theva,

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 

Open in new window


Roberto.
Roberto.

Avatar of Theva

ASKER

Hi,

Shows ambiguous error. Is that possible to run this procedure from “Private Sub Workbook”?
ASKER CERTIFIED SOLUTION
Avatar of Pabilio
Pabilio
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Theva

ASKER

Hi Roberto,

Thanks for the help.