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

noveske

Board Regular
Joined
Apr 15, 2022
Messages
120
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.


1689283583969.png
1689283554401.png
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Why are you using VBA Code to do this? You could use functions in the cells in Sheet2 as shown below.

GetLastRow.xlsm
ABC
11A
12B
13A
Sheet1
Cells with Data Validation
CellAllowCriteria
C11:C13ListA,B


GetLastRow.xlsm
AB
11X 
12 O
13X 
Sheet2
Cell Formulas
RangeFormula
A11:A13A11= IF(Sheet1!$C11="A","X","")
B11:B13B11= IF(Sheet1!$C11="B","O","")
 
Upvote 0
Or VBA
VBA Code:
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
 
Upvote 1
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.
VBA Code:
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
 
Upvote 1
Solution
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:
VBA Code:
  Sheets(2).Cells(Target.Row,1).Clear
  Sheets(2).Cells(Target.Row,2).Clear

In the first example change the code as follows:
VBA Code:
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
 
Upvote 1
Why are you using VBA Code to do this? You could use functions in the cells in Sheet2 as shown below.

GetLastRow.xlsm
ABC
11A
12B
13A
Sheet1
Cells with Data Validation
CellAllowCriteria
C11:C13ListA,B


GetLastRow.xlsm
AB
11X 
12 O
13X 
Sheet2
Cell Formulas
RangeFormula
A11:A13A11= IF(Sheet1!$C11="A","X","")
B11:B13B11= 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.
 
Upvote 0
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:
VBA Code:
  Sheets(2).Cells(Target.Row,1).Clear
  Sheets(2).Cells(Target.Row,2).Clear

In the first example change the code as follows:
VBA Code:
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
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top