Only allow 1 entry in a range. After entry the rest of the range auto fills

danbates

Active Member
Joined
Oct 8, 2017
Messages
377
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Please can someone help me?

I would like code that once 1 cell has been entered in a range, the rest of the range enters a "C".

So basically my range is D5:D15

If the operator enters a "N" then nothing happens.
If the operator enters a "Y" then the rest of the cells in the range auto fills with a "C", but if the "Y" is deleted then all the "C" are deleted as well.

I hope this makes sense.

Thanks
Dan
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Copy this to the worksheet code module of the sheet where you want to controll the range. Be sure your workbook is saved as a macro enabled workbook to preserve the code. The code will only execute the auto fill if "y" (case insensitive) is entered into cell D5.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("D5")) Is Nothing Then
        If UCase(Target.Value) = "Y" Then
            Target.Offset(1) = "C"
            Target.Offset(1).AutoFill Target.Offset(1).Resize(10, 1)
        End If
    End If
Application.EnableEvents = True
End Sub
 
Upvote 0
Hi JLGWhiz,

Thank you for the code you've done for me.

I have managed to alter it to my needs but I do have a question if that's ok?

Here is the altered code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
'If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
  If Not Intersect(Target, Range("D5:AH5")) Is Nothing Then
        If UCase(Target.Value) = "Y" Or LCase(Target.Value) = "y" Then
            Target.Offset(, 1) = "C"
 Target.Offset(, 1).AutoFill Target.Offset(, 1).Resize(1, 30)
    End If
      End If
    
    If Target.Value = 0 Or UCase(Target.Value) = "N" Or LCase(Target.Value) = "n" Then
    Target.Offset(, 1).ClearContents
            Target.Offset(, 1).AutoFill Target.Offset(, 1).Resize(1, 30)
            
    End If


Application.EnableEvents = True


End Sub

Say I enter the letter "N" in the first 12 cells ie D5 to O5 and then enter "Y" in cell 13 (P5) the code then puts the letter "C" in the next 30 cells to the right, taking it past the range of cells I want to keep it within.
Is there any way of keeping the offset range within the range I need it for (D5:AH5)?

Thanks again
Dan
 
Upvote 0
Code:
Or LCase(Target.Value) = "y"
This is redundant to this
Code:
UCase(Target.Value) = "Y"
They both desensitize the case of the characters.
If you have a fixed limit on the range, then use that range reference and instead of resizing use
Code:
Target.Offset(, 1).Autofill  Range(Target.Offset(, 1), "AH" & Target.Row)
The whole thing is base on making sure the cell with the fill value is included in the destination range. The destination range can be any form of reference that returns a valid range address that you want to fill.
 
Last edited:
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...t-offset-autofill-within-a-certain-range.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hi JLGWhiz,

Thank you for you reply and the explanation into solving my issue.

Thanks

Dan
 
Upvote 0
Hi Fluff,

Thank you for the heads up and I am sorry for breaking the site rules and I will not do it again.

Thanks

Dan
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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