PDA

View Full Version : Else If Loop



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

mikerickson
11-15-2011, 11:22 AM
I'm seeing something like

Range("K" & i).Value = Range("J" & i).Value + WorksheetFunction.VLOOKUP(Range("B"&i), Range("N:T"),7)(which will fail if N is not sorted ascending)

mdmackillop
11-15-2011, 12:27 PM
Can you post some data on which to run your code

maninjapan
11-15-2011, 02:18 PM
Here is an example of the data

mdmackillop
11-15-2011, 03:37 PM
Something to try
Option Explicit

Sub Tokyo_Time()
Dim i As Long, LastRow As Long, j As Long, x As Long
Dim Rng
Dim Dates
Dim Diff
Dim Tim
Dim Rslt()

Set Rng = Range("N26:N47")
Set Diff = Range("T26:T47")
Set Dates = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
Set Tim = Range(Cells(1, 10), Cells(Rows.Count, 10).End(xlUp))

ReDim Rslt(1 To Dates.Count + 1)
For i = 2 To Dates.Count
If Dates(i, 1) <= Rng(1, 1) Then
Rslt(1) = Tim(1, 1) + Diff(1)
Else
x = Application.Match(Dates(i, 1), Rng)
Rslt(i) = Tim(i, 1) + Diff(x)
End If
Next i
Range("K1").Resize(Dates.Count) = Application.Transpose(Rslt)
End Sub