Solved

Convert formula into macro

Posted on 2010-11-22
6
276 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now