Mcaa51
04-05-2010, 12:55 PM
Everyday I get a spread sheet that has generic values in column C that I need to map to another value based upon what is in Colum E. On another sheet I have the list of the values and what they need to be mapped to.
Currently I am doing this using a nested For statement, however, in the sheet that needs to have values mapped there are about 10,000 rows, so this takes quite a while.
Is there a more efficient way to do this?
Below is my current code
Sub MapNames()
Dim i As Integer
Dim j As Integer
Dim LastRowTrades As Integer
Dim LastRowThresh As Integer
Dim Trades As Worksheet
Dim Thresh As Worksheet
Dim myArray() As String
Dim AcctNum As String
Dim Name As String
Dim Piv As Worksheet
Set Trades = Worksheets("Trades")
Set Thresh = Worksheets("Thresholds")
Set Piv = Worksheets("Pivot")
LastRowTrades = Trades.Range("A65536").End(xlUp).Row
LastRowThresh = Thresh.Range("A65536").End(xlUp).Row
ReDim myArray(2 To LastRowThresh, 2)
Thresh.Select
For i = 2 To LastRowThresh
myArray(i, 1) = Cells(i, 1).Value
myArray(i, 2) = Cells(i, 3).Value
Next i
Trades.Select
For i = 2 To LastRowThresh
AcctNum = myArray(i, 1)
Name = myArray(i, 2)
For j = LastRowTrades To 2 Step -1
If Cells(j, 5).Value = AcctNum Then
Cells(j, 3).Value = Name
End If
Next j
Next i
Piv.Select
End Sub
Currently I am doing this using a nested For statement, however, in the sheet that needs to have values mapped there are about 10,000 rows, so this takes quite a while.
Is there a more efficient way to do this?
Below is my current code
Sub MapNames()
Dim i As Integer
Dim j As Integer
Dim LastRowTrades As Integer
Dim LastRowThresh As Integer
Dim Trades As Worksheet
Dim Thresh As Worksheet
Dim myArray() As String
Dim AcctNum As String
Dim Name As String
Dim Piv As Worksheet
Set Trades = Worksheets("Trades")
Set Thresh = Worksheets("Thresholds")
Set Piv = Worksheets("Pivot")
LastRowTrades = Trades.Range("A65536").End(xlUp).Row
LastRowThresh = Thresh.Range("A65536").End(xlUp).Row
ReDim myArray(2 To LastRowThresh, 2)
Thresh.Select
For i = 2 To LastRowThresh
myArray(i, 1) = Cells(i, 1).Value
myArray(i, 2) = Cells(i, 3).Value
Next i
Trades.Select
For i = 2 To LastRowThresh
AcctNum = myArray(i, 1)
Name = myArray(i, 2)
For j = LastRowTrades To 2 Step -1
If Cells(j, 5).Value = AcctNum Then
Cells(j, 3).Value = Name
End If
Next j
Next i
Piv.Select
End Sub