PDA

View Full Version : Mapping Values



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

mdmackillop
04-05-2010, 02:15 PM
Can you post a small sample of your data and setup