Updating column values based on other column values in excel VBA code

Question

0.00/5 (No votes)

See more:

Hi - Am trying to update some column values based on value of one column value by using this VBA code.

But am getting error that it's not properly ended.

Could you please help me to fix it?

What I have tried:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("AY" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("AY" & i).Value = "1478" and "1476" Then
            Range("BL" & i).Value = " XYZ "
        End If
           If Range("AY" & i).Value = "1589" And "1595" Then
            Range("BL" & i).Value = "ABC"
        End If
        If Range("AY" & i).Value = "484" and "1447" and "1695" Then
            Range("BL" & i).Value = "XZ"
        End If
        If Range("AY" & i).Value = "1447" Then
             Range("BM" & i).Value = "AZ" and Range("BL" & i).Value = "SPT" 
        End If        
    Next i
End Sub

Posted 26-Mar-18 9:27am

GJSS

Updated 26-Mar-18 9:32am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-03-26T09:34:00

Solution 1

This piece of code will never get true:

If Range("AY" & i).Value = "1478" and "1476" Then

A cell can not have both values in the same time! By the way: why do you think that value is string based?

[EDIT#1]

I'd change multiple If's into Select Case[^]:

VB

Select Case Range("AY" & i).Value
    Case 1476, 1478
            Range("BL" & i).Value = " XYZ "
    Case 1589, 1595
            Range("BL" & i).Value = "ABC"
    Case 484, 1447, 1695
            Range("BL" & i).Value = "XZ"
    Case 1447
             Range("BM" & i).Value = "AZ" 
             Range("BL" & i).Value = "SPT" 
End Select

which is equivalent to:

VB

If Range("AY" & i).Value = 1478 OR Range("AY" & i).Value = 1476 Then
        Range("BL" & i).Value = " XYZ "
End If
'and so on...

Do you see the difference?

[EDIT#2]
Another way is to use Dictionary object[^].

VB

'needs reference to MS Scripting Runtime
    Dim myDictionary As Dictionary
    
    Set myDictionary = New Dictionary
    With myDictionary
        .Add 1476, "XYZ"
        .Add 1478, "XYZ"
        .Add 1589, "ABC"
        .Add 1595, "ABC"
        '...
    End With
    'later...
    For i = 2 To LastRow
        Range("BL" & i).Value = myDictionary(Range("AY" & i).Value)
    Next

Posted 26-Mar-18 9:34am

Maciej Los

Updated 26-Mar-18 10:49am

v5

Comments

GJSS 26-Mar-18 15:37pm

Here am trying to update column BL with value "XYZ" based on the column AY with values 1478 and 1476

Maciej Los 26-Mar-18 15:52pm

No, you don't. You're checking if cell contains value of...

GJSS 26-Mar-18 16:16pm

Yes..if the value of AY = "1478" and "1476", then i want to update the value of column BL with "XYZ"

Maciej Los 26-Mar-18 16:23pm

As i mentioned... a cell can not have the same value in the same time... If there is a "1478", how does "1476" can be?
Think of it!

GJSS 26-Mar-18 16:27pm

Those are all column values where it has multiple values but here am considering two column values based on these values am trying to update column BY
AY
147839
147839
147697
147697
147697

Maciej Los 26-Mar-18 16:33pm

Check updated answer ;)

GJSS 26-Mar-18 17:45pm

Code with "select case" worked out. Thank you Maciej

Maciej Los 27-Mar-18 2:13am

You're very welcome.

Richard Deeming 27-Mar-18 8:56am

If Range("AY" & i).Value = "1478" and "1476" Then

That's not testing whether the cell is equal to two different values at the same time. It's testing whether the cell is equal to the first value, and the second value can be coerced to something that looks like True, using VB's "helpful" type coercion.

If (Range("AY" & i).Value = "1478") and ("1476") Then

:)

Maciej Los 27-Mar-18 9:45am

Good point, Richard! The second part of If statement evaluates into "something that looks like True".
Below code proves that:

Dim y As Integer() = {1478, 1476}
For i As Integer = 0 To y.Length-1
	Dim result As Boolean = y(i)=1478 And 1476
	Console.WriteLine("A statement ({0}=1478 And 1476) evaluates into {1}", y(i), result)
Next

Well... I was in hurry... ;)