Check values in a column and if condition matches then populate 3 other columns

aroy1195 · Jun 4th 2019

I want to write a macro which would check the value of cells in column f and fill column g, h and I cells if condition is met and this is for the entire column range.

The column f in total has 3 different values.

Say if- cell in column f has value x then I want column G cell to have AAA, column h cell to have BBB and column I to have CCC.

Similarly if Column F cell has value y then G, H and I to have AA, BB, CC respectively.

Can any one help me out please.

**KjBox** · Jun 4th 2019

Maybe

Code

Sub test()
    Dim x, i As Long
    
    With .Columns(6).SpecialCells(2).Resize(, 4)
        x = .Value
        For i = 1 To UBound(x, 1)
            If x(i, 1) = "x" Then
                x(i, 2) = "AAA": x(i, 3) = "BBB": x(i, 4) = "CCC"
            ElseIf x(i, 1) = "y" Then
                x(i, 2) = "AA": x(i, 3) = "BB": x(i, 4) = "CC"
            ElseIf x(i, 1) = "z" Then
                x(i, 2) = "A": x(i, 3) = "B": x(i, 4) = "C"
            End If
        Next
        .Value = x
    End With
    
End Sub

Display More

aroy1195 · Jun 5th 2019

Getting an error saying Invalid or unqualified reference on the 3rd line

**KjBox** · Jun 5th 2019

Can you attach your workbook.

aroy1195 · Jun 5th 2019

Can't upload the actual so have prepared a dummy sheet of the same. So what I want to do is check the values in column F and accordingly put values in column G, H, I.

Say, for F column cell value=x the corresponding parallel cell in G will have XXX, in H will have YYY and I will have ZZZ

**KjBox** · Jun 5th 2019

Please attach the workbook with the dummy sheet, not a PDF of it.

aroy1195 · Jun 5th 2019

Sorry, didn't notice it was a pdf... Here's the xlsx

**KjBox** · Jun 5th 2019

Sorry, it was my bad with the code I provided, I left out the sheet reference. This works

Code

Sub test()
    Dim x, i As Long
    
    With ActiveSheet.Columns(6).SpecialCells(2).Resize(, 4)
        x = .Value
        For i = 1 To UBound(x, 1)
            If x(i, 1) = "x" Then
                x(i, 2) = "AAA": x(i, 3) = "BBB": x(i, 4) = "CCC"
            ElseIf x(i, 1) = "y" Then
                x(i, 2) = "AA": x(i, 3) = "BB": x(i, 4) = "CC"
            ElseIf x(i, 1) = "z" Then
                x(i, 2) = "A": x(i, 3) = "B": x(i, 4) = "C"
            End If
        Next
        .ClearContents
        .Value = x
    End With
    
End Sub

Display More

**KjBox** · Jun 5th 2019

This better

Code

Sub test()
    Dim x, i As Long
    
    With ActiveSheet.Cells(1).CurrentRegion
        .Offset(1, 6).Resize(, 3).ClearContents
        x = .Value
        For i = 2 To UBound(x, 1)
            If x(i, 6) = "x" Then
                x(i, 7) = "AAA": x(i, 8) = "BBB": x(i, 9) = "CCC"
            ElseIf x(i, 6) = "y" Then
                x(i, 7) = "AA": x(i, 8) = "BB": x(i, 9) = "CC"
            ElseIf x(i, 6) = "z" Then
                x(i, 7) = "A": x(i, 8) = "B": x(i, 9) = "C"
            End If
        Next
        .Value = x
    End With
    
End Sub

Display More

aroy1195 · Jun 5th 2019

Thank you for your help [USER="33159"]KjBox[/USER] that worked

**KjBox** · Jun 5th 2019

You're welcome.

Check values in a column and if condition matches then populate 3 other columns

Participate now!

Share