maninjapan
11-14-2011, 11:51 PM
I am running a macro that is converting a number of years of data from 2 time zones and adjusting for Daylight Saving changes. I am using a number of ElseIf statements, and it seems to get mired down very quickly. I have tested with a sample of data (50000 rows) and it works ok, however the full set of data contains just over 170,000 rows of data. I would like to break it up into at least 2 pieces but not quite sure of the correct format. Running it on chunks of data at a time is probably the easiest solution, but I would like to try and find a solution that will run on the data as it stands.
I'm sure there are probably a number of more efficient ways to tackle this, however this is the extent of my VBA abilities.
Any help would be much appreciated.
Sub Tokyo_Time()
Dim i As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range("B" & i).Value <= Range("N26").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T26").Value
ElseIf Range("B" & i).Value > Range("N26").Value And Range("B" & i).Value <= Range("N27").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T26").Value
ElseIf Range("B" & i).Value > Range("N27").Value And Range("B" & i).Value <= Range("N28").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T27").Value
ElseIf Range("B" & i).Value > Range("N28").Value And Range("B" & i).Value <= Range("N29").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T28").Value
ElseIf Range("B" & i).Value > Range("N29").Value And Range("B" & i).Value <= Range("N30").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T29").Value
ElseIf Range("B" & i).Value > Range("N30").Value And Range("B" & i).Value <= Range("N31").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T30").Value
ElseIf Range("B" & i).Value > Range("N31").Value And Range("B" & i).Value <= Range("N32").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T31").Value
ElseIf Range("B" & i).Value > Range("N32").Value And Range("B" & i).Value <= Range("N33").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T32").Value
ElseIf Range("B" & i).Value > Range("N33").Value And Range("B" & i).Value <= Range("N34").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T33").Value
ElseIf Range("B" & i).Value > Range("N34").Value And Range("B" & i).Value <= Range("N35").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T34").Value
ElseIf Range("B" & i).Value > Range("N35").Value And Range("B" & i).Value <= Range("N36").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T35").Value
ElseIf Range("B" & i).Value > Range("N36").Value And Range("B" & i).Value <= Range("N37").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T36").Value
ElseIf Range("B" & i).Value > Range("N37").Value And Range("B" & i).Value <= Range("N38").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T37").Value
ElseIf Range("B" & i).Value > Range("N38").Value And Range("B" & i).Value <= Range("N39").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T38").Value
ElseIf Range("B" & i).Value > Range("N39").Value And Range("B" & i).Value <= Range("N40").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T39").Value
ElseIf Range("B" & i).Value > Range("N40").Value And Range("B" & i).Value <= Range("N41").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T40").Value
ElseIf Range("B" & i).Value > Range("N41").Value And Range("B" & i).Value <= Range("N42").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T41").Value
ElseIf Range("B" & i).Value > Range("N42").Value And Range("B" & i).Value <= Range("N43").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T42").Value
ElseIf Range("B" & i).Value > Range("N43").Value And Range("B" & i).Value <= Range("N44").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T43").Value
ElseIf Range("B" & i).Value > Range("N44").Value And Range("B" & i).Value <= Range("N45").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T44").Value
ElseIf Range("B" & i).Value > Range("N45").Value And Range("B" & i).Value <= Range("N46").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T45").Value
ElseIf Range("B" & i).Value > Range("N46").Value And Range("B" & i).Value <= Range("N47").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T46").Value
ElseIf Range("B" & i).Value > Range("N47").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T47").Value
End If
Next i
Range("J" & i & ":J" & LastRow).NumberFormatLocal = "hh:mm"
End Sub
I'm sure there are probably a number of more efficient ways to tackle this, however this is the extent of my VBA abilities.
Any help would be much appreciated.
Sub Tokyo_Time()
Dim i As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range("B" & i).Value <= Range("N26").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T26").Value
ElseIf Range("B" & i).Value > Range("N26").Value And Range("B" & i).Value <= Range("N27").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T26").Value
ElseIf Range("B" & i).Value > Range("N27").Value And Range("B" & i).Value <= Range("N28").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T27").Value
ElseIf Range("B" & i).Value > Range("N28").Value And Range("B" & i).Value <= Range("N29").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T28").Value
ElseIf Range("B" & i).Value > Range("N29").Value And Range("B" & i).Value <= Range("N30").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T29").Value
ElseIf Range("B" & i).Value > Range("N30").Value And Range("B" & i).Value <= Range("N31").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T30").Value
ElseIf Range("B" & i).Value > Range("N31").Value And Range("B" & i).Value <= Range("N32").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T31").Value
ElseIf Range("B" & i).Value > Range("N32").Value And Range("B" & i).Value <= Range("N33").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T32").Value
ElseIf Range("B" & i).Value > Range("N33").Value And Range("B" & i).Value <= Range("N34").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T33").Value
ElseIf Range("B" & i).Value > Range("N34").Value And Range("B" & i).Value <= Range("N35").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T34").Value
ElseIf Range("B" & i).Value > Range("N35").Value And Range("B" & i).Value <= Range("N36").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T35").Value
ElseIf Range("B" & i).Value > Range("N36").Value And Range("B" & i).Value <= Range("N37").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T36").Value
ElseIf Range("B" & i).Value > Range("N37").Value And Range("B" & i).Value <= Range("N38").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T37").Value
ElseIf Range("B" & i).Value > Range("N38").Value And Range("B" & i).Value <= Range("N39").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T38").Value
ElseIf Range("B" & i).Value > Range("N39").Value And Range("B" & i).Value <= Range("N40").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T39").Value
ElseIf Range("B" & i).Value > Range("N40").Value And Range("B" & i).Value <= Range("N41").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T40").Value
ElseIf Range("B" & i).Value > Range("N41").Value And Range("B" & i).Value <= Range("N42").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T41").Value
ElseIf Range("B" & i).Value > Range("N42").Value And Range("B" & i).Value <= Range("N43").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T42").Value
ElseIf Range("B" & i).Value > Range("N43").Value And Range("B" & i).Value <= Range("N44").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T43").Value
ElseIf Range("B" & i).Value > Range("N44").Value And Range("B" & i).Value <= Range("N45").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T44").Value
ElseIf Range("B" & i).Value > Range("N45").Value And Range("B" & i).Value <= Range("N46").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T45").Value
ElseIf Range("B" & i).Value > Range("N46").Value And Range("B" & i).Value <= Range("N47").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T46").Value
ElseIf Range("B" & i).Value > Range("N47").Value Then
Range("K" & i).Value = Range("J" & i).Value + Range("T47").Value
End If
Next i
Range("J" & i & ":J" & LastRow).NumberFormatLocal = "hh:mm"
End Sub