How to check values in a column and if condition matches then populate 3 other columns
Category: [Excel] Demo Available
How to check values in a column and if condition matches then populate 3 other columns
Requirement:
The user wants 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 the user wants 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.
The user wants 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 the user will have ZZZ.
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create VBA for index and match based on sheet criteria |
| How to copy and paste column in wkbk 1 if its cell has text which matches with a cell of wbk 2 |
| How to compare 2 columns in different worksheets and overwrite existing record if a match found |
| How to compare 2 columns align matches (retaining formula) move columns 3 to 6 with column 2 |
| How to use a UserForm: CheckBox Checked if Listbox column 5 text matched CheckBox text |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.