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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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

L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
121
Legacy 519704
L
S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
270
MARK858
MARK858
S
  • Question Question
Trouble with macro to delete rows
2
Replies
14
Views
246
Jeffrey Mahoney
Jeffrey Mahoney
C
  • Solved
VBA - Copy and paste a range of values N times
Replies
6
Views
440
cydmm
C
P
  • Question Question
How to find till which row the marco executed. It stopped at some random row without any error and unable to identitfy at what row it has been stopped
Replies
3
Views
191
FormR
FormR

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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