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

 

[RoryA]

Welcome to the forum.

Is there a reason you can't use formulas and a lookup table for this?

 

[Shooty1976]

Good morning RoryA,
It's a good question.
Just thinking it through:
I could certainly have a table containing the reference numbers and the correct values.
However, as not all rows need altering, would there not still need to be a macro which looks at "C" to see whether the row in question is one of the ones which needs altering?
The majority of rows don't need altering, so I couldn't just put a formula in every cell in column AL to vlookup to the table of correct values.
Am I missing something?

 

[My Aswer Is This]

You could use something like this it would take out all the else statements:

Sub PopulateOne()
'Modified 5/23/2018 4:43 AM  EDT
last = Cells(Rows.Count, "a").End(xlUp).Row
    For i = 2 To last
        If Cells(i, "c").Value = "1" Then Cells(i, "al").Value = 547
        If Cells(i, "c").Value = "2" Then Cells(i, "al").Value = 65
        If Cells(i, "c").Value = "3" Then Cells(i, "al").Value = 24
        If Cells(i, "c").Value = "59976" Then Cells(i, "al").Value = 41115
Next
End Sub

 

[My Aswer Is This]

My script may also not do what you want.
Can you enter all the values in a range like Y1:Y20 and replace Value in range Z1:Z20

Then the script would loops through this range then look through Column C Range

 

[njimack]

If there's a rule to which rows need altering, this can (probably) be built into your formula.

Another approach to If/Else is Select Case...

Select Case Cells(i, "c")
    Case "59976": Cells(i, "c") = "41115"
    Case "60049": Cells(i, "c") = "64127"
    'etc
    'etc
End Select

 

[Shooty1976]

Ooooooh, THAT looks promising. Thank you Njimack, that might be exactly what I'm after. Will recode and have a time comparison to see if it's quicker.
Say I wanted to alter two cells rather than just one, would I separate by a :?
So, like case "59976": cells(i, "al").value = "41115": cells(i, "am").value = "21345"
Will have a play.
Thanks again.

 

[njimack]

To carry out more than one alteration:

Case "59976"
Cells(i,"al") = "41115"
Cells(I,"am")="21345"
'etc
End Select

 

[My Aswer Is This]

The more I think about this;
We really do not know what row "456" may be found on.
So we will have to loop through all rows looking for each value

So how many of the values you want to look for?

Is it like 10 or 100

I still think you will need to enter the values to look for in a range of cells then we will have to loop through each row for each value.

Then we will use find to find each value and modify al to the correct value.

 

[RoryA]

I'd put the formula into a different column, and return the value currently in AL if there isn't a match in the lookup table. So something like:

=IFERROR(VLOOKUP(C2,lookuptablerange,2,false),AL2)

copied down. You can then just copy and paste the results over column AL as values if you like.