Solved

Convert formula into macro

Posted on 2010-11-22
6
312 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Theva
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:Pabilio
ID: 34193878
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
0
 

Author Comment

by:Theva
ID: 34193898
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

0
 
LVL 5

Expert Comment

by:Pabilio
ID: 34193960
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.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Theva
ID: 34194326
Hi,

Shows ambiguous error. Is that possible to run this procedure from “Private Sub Workbook”?
0
 
LVL 5

Accepted Solution

by:
Pabilio earned 500 total points
ID: 34196690
Hi Theva,

I'm triying to reproduce the error here, but is working fine...
It place the time in column D (4) in the format 0:00:00 0:00:00
And also place the formula you need in columns AB and AC
The problem could be with the Userform1 code...

Here is the code to use from the Private sub workbook... but I'm not sure it is going to work if the changes in Column Z are made by code...give it a try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sheets("Time")
Dim RAN As Range, RAN2 As Range
Set RAN = Range("Z13:AA36")
If Intersect(Target, RAN) Is Nothing Then Exit Sub
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))"
End With
End Sub

Open in new window


If the problem continue, post your file with all codes and userform to check the problem.

Regards.
Roberto.
0
 

Author Closing Comment

by:Theva
ID: 34198016
Hi Roberto,

Thanks for the help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question