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

[danbates]

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

 

[JLGWhiz]

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.

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

 

[danbates]

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:

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

 

[JLGWhiz]

Or LCase(Target.Value) = "y"

This is redundant to this

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

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.

 

[Fluff]

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.

 

[danbates]

Hi JLGWhiz,

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

Thanks

Dan

 

[danbates]

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

 

[JLGWhiz]

Hi JLGWhiz,

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

Thanks

Dan

You're welcome,
regards, JLG