Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert formula into macro

Posted on 2010-11-22
6
Medium Priority
?
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

618 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