Novice Needs Help with Macro

M

mbrown279

New Member
Joined
Sep 16, 2018
Messages
10
Hello All, First let me state that I am a complete novice to macros and vba. However, I am able to reverse engineer existing macros and vba code to en extent. I was able to locate the macro below from this this forum. I made some changes to suit my needs and it works beautifully.

My problem is that the macro will not rerun when there is new call data added to the table. The macro converts all of the existing data, but it will not convert new time data added to it.

Example:
9/1 data is converted from seconds
9/2 added data for this date, but macro will not run again it says already converted.

Here is the macro I am using currently, but I want to be able to convert newly added data to the sheet .
Code: 
Sub Test()
    Dim LR As Long
    Dim r As Long
    With Worksheets("Sheet1")
        If .Range("E3").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = 2 To LR
            With .Range("D" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("E" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("F" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("G" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("I" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("J" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("K" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("L" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("M" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("N" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("P" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("R" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("Z" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("AA" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
        Next r
    End With
End Sub
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Try removing this part
Code: 
 If .Range("E3").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If
 
Upvote 0
Fluff said:
Try removing this part
Code: 
 If .Range("E3").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If
Click to expand...

Thank you so much for responding so quickly. I have a two questions.

1. By removing the code will it attempt to convert all of the data again?
2. Do I need Range ("E3) to tell the code where to begin?
 
Upvote 0
1. By removing the code will it attempt to convert all of the data again?
Click to expand...
Excellent point, yes it will
Try
Code: 
Lr = .Cells(.Rows.count, 1).End(xlUp).Row
For r = .Range("AB1").Value To Lr
and add this line as shown
Code: 
   Next r
   .Range("AB1").Value = Lr+1
End With
Change AB1 to a blank cell
 
Upvote 0
Fluff said:
Excellent point, yes it will

Thank you so much for responding to my post. I really appreciate it, I tried using the information provide but I am getting a compile error "invalid or unqualified reference".

Code: 
Sub Convert()
    Dim LR As Long
    Dim r As Long
    With Worksheets("CMS")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = .Range("AB3").Value To LR
    Next r
    .Range("AB3").Value = LR + 1
    End With
            With .Range("E" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("F" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("G" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("H" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("I" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("J" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("K" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("N" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("O" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("P" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("Q" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("R" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("S" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("T" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("U" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("V" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("W" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
        Next r
    End With
End Sub
Click to expand...
 
Last edited by a moderator:
Upvote 0
When posting code please use code tags, the # icon in the reply window.

Remove this 3 lines from where you have put them
Code: 
   Next r
   [COLOR=#0000ff].Range("AB1").Value = Lr+1[/COLOR]
End With
And then insert the line in blue at the bottom of your code, between the other 2 lines
 
Upvote 0
My I apologies for the improper posting etiquette. I have corrected the code, now I am getting a run time error '1004' Application-declined or object defined error. When I click debug it highlights line 7 .range "E"
Code: 
Sub Test()    Dim LR As Long
    Dim r As Long
    With Worksheets("CMS")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = .Range("AB3").Value To LR
            With .Range("E" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("F" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("G" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("H" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("I" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("J" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("K" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("N" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("O" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("P" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("Q" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("R" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("S" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("T" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("U" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("U" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("V" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("W" & r)
                .Value = .Value / 86400
                .NumberFormat = "[hh]:mm:ss"
            End With
        Next r
        .Range("AB3").Value = LR + 1
    End With
End Sub
 
Upvote 0
What is the value of r when you get the error?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
how to add my signature with images to macro
Replies
1
Views
168
Domenic
D
M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
434
Alex Blakenburg
A
I
  • Solved
Moving Data based off a condition
Replies
7
Views
158
ItalianPlatinum
I
tezza
  • Question
Option explicit workbook help
Replies
3
Views
462
igold
igold
R
  • Question
VBA to run macro with the most recent events
Replies
4
Views
192
rafaelbatalha
R

Forum statistics

Threads
1,214,918
Messages
6,122,257
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top