VBA - If, Then - output to Sheet2 depending on input on Sheet1.

[noveske]

Sheet1 is an input sheet.
Sheet2 is where the data outputs.

Using a Data Validation List to select an option on Sheet 1.
Drop down list options are A and B.

Is there a way to set VBA conditions where the selection of Sheet1.C1 would output an X on Sheet2.A1 and B would output an O in Sheet2:B1?

If Sheet1.C1=A then Sheet2.A1=X
If Sheet1.C1=B then Sheet2.B1=O

Then would repeat Sheet1.C2 would be to Sheet2.A1 / Sheet2.B1.

If Sheet1.C2=A then Sheet2.A2=X
If Sheet1.C2=B then Sheet2.B2=O

A clean way to set a range and output to corresponding row without copying the statements to check for each row?

Syntax errors from working on outputting to another sheet and the multiple cell range.

 

[Bosquedeguate]

Why are you using VBA Code to do this? You could use functions in the cells in Sheet2 as shown below.

GetLastRow.xlsm
	A	B	C
11			A
12			B
13			A
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C11:C13	List	A,B

GetLastRow.xlsm
	A	B
11	X
12		O
13	X
Sheet2

Cell Formulas
Range	Formula
A11:A13	A11	= IF(Sheet1!$C11="A","X","")
B11:B13	B11	= IF(Sheet1!$C11="B","O","")

 

[Skyybot]

Or VBA

Sub shtCopy()
Dim wb As Workbook, sht1 As Worksheet, sht2 As Worksheet
Dim data As Range, outPut As Range, cell As Range
Set wb = Workbooks("Your_Workbook_Name.xlsx") 'Change to suit your needs
Set sht1 = wb.Sheets(1)
Set sht2 = wb.Sheets(2)
Set data = sht1.Range(sht1.Cells(1, 3), sht1.Cells(sht1.UsedRange.Rows.Count, 3))
For Each cell In data
    If cell.Value = "A" Then
        sht2.Cells(cell.Row, 1) = "X"
    ElseIf cell.Value = "B" Then
        sht2.Cells(cell.Row, 2) = "O"
    End If
Next cell
End Sub

 

[My Aswer Is This]

If you want a Vba solution:
Here is a sheet change event script.
To install right script on sheet1 tab and select view Code.
And paste in this script.
The script runs when you enter A or B in column C of any row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then

Select Case Target.Value
    Case "A": Sheets(2).Cells(Target.Row, 1).Value = "X"
    Case "B": Sheets(2).Cells(Target.Row, 2).Value = "O"
    End Select
    End If
End Sub

 

[Bosquedeguate]

In both of the VBA examples given code will be needed to Clear columns A and B of the affected row.
If this code is not added you can if both "X" and "O" on the same row if the value of A/B changes.
Add code like the following:

Above the Select statement in the second example:

  Sheets(2).Cells(Target.Row,1).Clear
  Sheets(2).Cells(Target.Row,2).Clear

In the first example change the code as follows:

For Each cell In data
    '******** Add these two rows *********
    sht2.Cells(cell.Row, 1).Clear
    sht2.Cells(cell.Row, 2).Clear
    '******** Add two rows above *********
    If cell.Value = "A" Then
        sht2.Cells(cell.Row, 1) = "X"
    ElseIf cell.Value = "B" Then
        sht2.Cells(cell.Row, 2) = "O"
    End If
Next cell

 

[noveske]

Why are you using VBA Code to do this? You could use functions in the cells in Sheet2 as shown below.

GetLastRow.xlsm
	A	B	C
11			A
12			B
13			A
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C11:C13	List	A,B

GetLastRow.xlsm
	A	B
11	X
12		O
13	X
Sheet2

Cell Formulas
Range	Formula
A11:A13	A11	= IF(Sheet1!$C11="A","X","")
B11:B13	B11	= IF(Sheet1!$C11="B","O","")

Yeah, I ended up going this route after realizing it due to cell placement.


Thanks for the responses everyone! Going through the VBA examples to figure out what was causing my initial VBA code to error.

 

[noveske]

In both of the VBA examples given code will be needed to Clear columns A and B of the affected row.
If this code is not added you can if both "X" and "O" on the same row if the value of A/B changes.
Add code like the following:

Above the Select statement in the second example:

  Sheets(2).Cells(Target.Row,1).Clear
  Sheets(2).Cells(Target.Row,2).Clear

In the first example change the code as follows:

For Each cell In data
    '******** Add these two rows *********
    sht2.Cells(cell.Row, 1).Clear
    sht2.Cells(cell.Row, 2).Clear
    '******** Add two rows above *********
    If cell.Value = "A" Then
        sht2.Cells(cell.Row, 1) = "X"
    ElseIf cell.Value = "B" Then
        sht2.Cells(cell.Row, 2) = "O"
    End If
Next cell

Didn't even think of that. Very useful.

Or VBA

Sub shtCopy()
Dim wb As Workbook, sht1 As Worksheet, sht2 As Worksheet
Dim data As Range, outPut As Range, cell As Range
Set wb = Workbooks("Your_Workbook_Name.xlsx") 'Change to suit your needs
Set sht1 = wb.Sheets(1)
Set sht2 = wb.Sheets(2)
Set data = sht1.Range(sht1.Cells(1, 3), sht1.Cells(sht1.UsedRange.Rows.Count, 3))
For Each cell In data
    If cell.Value = "A" Then
        sht2.Cells(cell.Row, 1) = "X"
    ElseIf cell.Value = "B" Then
        sht2.Cells(cell.Row, 2) = "O"
    End If
Next cell
End Sub

Was not able to get this to run. Changed the name of the workbook.
Tried by adding this to the Sheet itself and This Workbook.
Even tried save.

 

[My Aswer Is This]

I assume you have an answer that works for you. You marked my answer as the solution.

I never added a clear solution because you never said you may want the values cleared to begin with.
Take care and wish you the best.

I hope you try reading and understanding the code so in time you will learn from this and be able to modify code if needed and even learn to write some of your own code.