Macro: Autopopulate Cells based on input in one cell

[decksie]

I need help with a macro. I've found lots of macros online that a described to do almost what I want but my VB grasp is never enough to modify it to my needs. I've bought a VBA book to teach mycself but I'm afraid I need this spreadsheet too soon to wait for me to upskill.

I'm creating a date entry spreadsheet that needs to accept data that I manually input from completed questionnaires. In short


In cell A2, I will enter a value 'Yes' or 'No'. I've made a drop down box through data validation.
If I enter 'No', I want to autopopulate cells A3:A9 and A12:A18 with "N/A"
If I enter 'Yes' I want those aforementioned cells to remain blank so that I can manually populate them.
I'd like this code to work equally for each column with the corresponding rows affected.

As a bit of bonus functionality, if it isn't difficult, If I accidentally enter 'No' in cell E2 and it autopopulates E3:E9 and E12:E18, if I change it back to 'Yes', can it cleat those N/As that shouldn't be there?

Thanks,
DD

 

[My Aswer Is This]

I do not understand this:
You said:

I'd like this code to work equally for each column with the corresponding rows affected.
Each column??

You know we have 16384 columns

So this script will work on all 16384 columns

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/27/2019  8:36:49 AM  EDT
If Target.Cells.Row = 2 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "No" Then
    Target.Offset(1).Resize(7).Value = "NA"
    Target.Offset(10).Resize(7).Value = "NA"
End If
If Target.Value = "Yes" Then
    Target.Offset(1).Resize(7).Value = ""
    Target.Offset(10).Resize(7).Value = ""
End If
End If
End Sub

​

 

[My Aswer Is This]

Or if you like using Case:
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/27/2019  8:49:49 AM  EDT
If Target.Cells.Row = 2 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Select Case Target.Value
    Case "No"
        Target.Offset(1).Resize(7).Value = "NA"
        Target.Offset(10).Resize(7).Value = "NA"
    Case "Yes"
        Target.Offset(1).Resize(7).Value = ""
        Target.Offset(10).Resize(7).Value = ""
End Select
End If
End Sub

 

[decksie]

Thanks very much, this works a treat and is very clear
Is there any advantage to using the 'case' function over the first way?

 

[decksie]

If it says 'no' it populates and when I change it back to 'yes' it clears, exactly as I originally requested. Is it possible to change that line that if it is changed from 'no' to anything else (not just 'yes') it clears? If not, could you teach me to make it an OR statement ie. if it is 'yes' OR 'gumbo' OR 'fridge' it clears.

Thanks again

 

[My Aswer Is This]

Try this:


If you enter anything other then "No" the script clears the range

Case is just something easier to write.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/28/2019  10:22:27 AM  EDT
If Target.Cells.Row = 2 Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value
    Case "No"
        Target.Offset(1).Resize(7).Value = "NA"
        Target.Offset(10).Resize(7).Value = "NA"
     Case Else
        Target.Offset(1).Resize(7).Value = ""
        Target.Offset(10).Resize(7).Value = ""
End Select
End If
End Sub

 

[Rick Rothstein]

Try this:


If you enter anything other then "No" the script clears the range

Case is just something easier to write.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/28/2019  10:22:27 AM  EDT
If Target.Cells.Row = 2 Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value
    Case "No"
        Target.Offset(1).Resize(7).Value = "NA"
        Target.Offset(10).Resize(7).Value = "NA"
     Case Else
        Target.Offset(1).Resize(7).Value = ""
        Target.Offset(10).Resize(7).Value = ""
End Select
End If
End Sub

I am not sure the following code will be understandable by someone new to VBA, but your above code can be written as a one-liner...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 2 And Target.CountLarge = 1 Then Intersect([3:9,12:18], Target.EntireColumn) = Left("N/A", -3 * (Target.Value = "No"))
End Sub[/td]
[/tr]
[/table]

Note to 'decksie'... as written, both my code and the code 'My Aswer Is This' posted only put the N/A into the cell if the user types "No" exactly (capital "N" lower case "o")... any other variation will cause the cells to be cleared.

 

[My Aswer Is This]

Yes I always try to write code Me and others understand. I only understand about 1% of code written like yours. But then others only like short code even if they do not understand it.

I am not sure the following code will be understandable by someone new to VBA, but your above code can be written as a one-liner...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 2 And Target.CountLarge = 1 Then Intersect(Range("3:9,12:18"), Target.EntireColumn) = Left("N/A", -3 * (LCase(Target.Value) = "no"))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

In case you want to use a lot of if statements it's easier to use case like this:
You will see I entered 4 values. You could do more.

This can be used in a lot of other situations where you want to have several other if statements.
But then I'm sure there are 25 other ways to do things like this also.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/28/2019  11:12:29 AM  EDT
If Target.Cells.Row = 2 Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value
    Case "No"
        Target.Offset(1).Resize(7).Value = "NA"
        Target.Offset(10).Resize(7).Value = "NA"
     Case "Alpha", "Bravo", "Charlie", "Delta"
        Target.Offset(1).Resize(7).Value = ""
        Target.Offset(10).Resize(7).Value = ""
End Select
End If
End Sub

 

[decksie]

Thank you both

As 'My answer is this' says, the longer code is great for a learner like me who has a better chance of understanding the syntax and logic, making minor adjustments relatively easy if necessary. But of course, I hope to get to the level one day where one line of succinct code such as Rick proposed is all I need to solve these problems!

Cheers,
Dan