Novice Needs Help with Macro

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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try removing this part
Code:
 If .Range("E3").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If
 
Upvote 0
Try removing this part
Code:
 If .Range("E3").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If

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

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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