?
Solved

Convert formula into macro

Posted on 2010-11-22
6
Medium Priority
?
327 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

764 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