Alternative to If... elseif... elseif... ?

[Shooty1976]

Good morning all,
New member here. My job involves cleansing a data set before it's provided to a client. The data is pulled from our system through SQL report, into a spreadsheet.
I've kind of fallen into this role: I'm a lawyer, but someone needs to do this to keep the client happy, and I'm the only one who a) wants to and b) is any good at it. Luckily, I like it far more than I ever liked the law.
So, anyway, at first I found problems with the data manually.
Then I thought "there's got to be an easier way to do this", which lead me to discover Excel VBA and Macros.
And, through sites like this (longtime lurker), I've taught myself VBA to a degree.
So, thank you.

My question is this:
My data set runs to 23,000 rows.
Each row represents 1 claim, and has a unique reference number.
For a few hundred rows, it is necessary to change some of the values. I won't bore you with the reason.
My macros work by looking for the unique reference number, and when they find it, they replace certain values in that row.
Here is some example code:

Sub PopulateOne()


last = Cells(Rows.Count, "a").End(xlUp).Row
For i = last To 2 Step -1


If Cells(i, "c").Value = "59976" Then
Cells(i, "al").Value = 41115
ElseIf Cells(i, "c").Value = "60049" Then
Cells(i, "al").Value = 7000
ElseIf Cells(i, "c").Value = "64127" Then
Cells(i, "al").Value = 55000
ElseIf Cells(i, "c").Value = "61237" Then
Cells(i, "al").Value = 75000
ElseIf Cells(i, "c").Value = "63599" Then
Cells(i, "al").Value = 2250000
etc

So, as you can see, really simple. "C" is the unique identifier. "AL" is the value that needs updating.
There are, as I say, a few hundred of these.
As I understand it, for each row, excel runs through ALL of the possible C values to see if any of them match that particular row.
That takes rather a long time.
Is it possible to eliminate the "if, else if, else if, else if" and make it more like:

Find row where cell C value = 63599
in that row, replace cell AL value with 2250000

So, specific targeting, rather than the scattergun approach I've got at present?

Thanks,
Rob

 

[Fazza]

By having a lookup table, an option is to have your VBA execute an UPDATE query. It could have SQL like below. This would avoid looping.

UPDATE BigTable B INNER JOIN LookupTable L ON B.RefNumberField = L.RefNumberField
SET B.Field_AL = L.FieldWithNewValue