VBA Help - Have code apply to specific cell range

[surkdidat]

Good afternoon

I have the following code which presently populates any value put in column B down a certain amount of lines, however, I need this only to apply to a certain range (from B75 to B1500)

Can I ask what I need to amend to apply to only a certain cell range please?

Also, I have a Macro (currently called Button9_Click ) that is currently affected by this, and comes up with a RunTime Error 13 - Type Mismatch) - this code worked perfectly fine before the below code was added to the worksheet - again can anyone advise the best way around this please?


(Code for the above:-

If Target.Count = 1 And Target.Column = 2 And Target <> "" Then
Application.EnableEvents = False
Range(Target.Offset(1, 0), Target.Offset(13, 0)) = Target
Application.EnableEvents = True

End If

 

[My Aswer Is This]

You did not provide the entire script.

You should show us both scripts.

I suppose the code you showed us is a sheet change event of some type

Is there some reason why you do not want to show us the entire script?

 

[surkdidat]

Apologies, the clear sheet script is as follows:-

Sub CALCULATOR_Button9_Click()
Range("B15:N65").Select
ActiveWorkbook.Worksheets("CALCULATOR").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("CALCULATOR").Sort.SortFields.Add Key:=Range( _
"B16:B75"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CALCULATOR").Sort
.SetRange Range("B15:d75")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("C9").Select
Selection.ClearContents
Range("c11").Select
Selection.ClearContents
Range("L8").Select
Selection.ClearContents
Range("L9").Select
Selection.ClearContents
Range("L10").Select
Selection.ClearContents




Range("E10").Select
Selection.ClearContents
Range("e12").Select
Selection.ClearContents

Range("O11").Select
Selection.ClearContents
Range("O12").Select
Selection.ClearContents
Range("N66").Select
Selection.ClearContents
Range("c16").Select


Range("c9").Select
End Sub

The code already provided is in its entirety, and sits on the worksheet itself.

You did not provide the entire script.

You should show us both scripts.

I suppose the code you showed us is a sheet change event of some type

Is there some reason why you do not want to show us the entire script?

 

[My Aswer Is This]

These two lines of code show the range:

"[COLOR=#ff0000]B16:B75[/COLOR]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CALCULATOR").Sort
        .SetRange Range("[COLOR=#ff0000]B15:d75[/COLOR]")

You said:

I have the following code which presently populates any value put in column B down a certain amount of lines,

This script does not populate any thing.

It does a sort and then clears several ranges.

 

[surkdidat]

Apologies, I think I am confusing issues with two problems

The below script (which is in full) when attached to the worksheet itself, will auto-populate whatever value is put into column B 13 times. So if you typed A into cell B1, then it would put A in allc ells in column B from B2 to B14.

What I need is the below code to be amended so that it will only work from B75 onwards. So if you entered anything in cells B1 to B74 then the code would not run, and if you entered it in any cell (in column B) from B75 and downwards, then it would autopopulate a further 13 times.

(I will forget the other issue with the longer "Clear" code for now to avoid confusing matters!!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 2 And Target <> "" Then
Application.EnableEvents = False
Range(Target.Offset(1, 0), Target.Offset(13, 0)) = Target
Application.EnableEvents = True

End If
End Sub

Apologies, the clear sheet script is as follows:-



The code already provided is in its entirety, and sits on the worksheet itself.

 

[Fluff]

When posting code please use code tags rather than quotes. It's the # icon in the reply window.
For your event code (assuming it's a worksheet_change event) try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B75:B1500")) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
    If Target <> "" Then
        Application.EnableEvents = False
        Range(Target.Offset(1, 0), Target.Offset(13, 0)) = Target
        Application.EnableEvents = True
    End If
End Sub

And for your button code add these 2 lines one at the very top & the other at the bottom.

        Application.EnableEvents = False
        Application.EnableEvents = True

 

[My Aswer Is This]

Try this:
See added code marked in red.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 2 And Target <> "" And Target.Row [COLOR=#ff0000]> 74 [/COLOR]Then
Application.EnableEvents = False
Range(Target.Offset(1, 0), Target.Offset(13, 0)) = Target
Application.EnableEvents = True
End If
End Sub

 

[My Aswer Is This]

You asked for B74 and onward

So I used >74 which does what you want

This line of code provided by others
Range("B75:B1500"))

Only allows it to work from row 75 to row 1500.

I saw no such request.

 

[Fluff]

It came from the 2nd line of the op

I need this only to apply to a certain range (from B75 to B1500)

 

[My Aswer Is This]

Well in a later post he said:
What I need is the below code to be amended so that it will only work from B75 onwards.

It came from the 2nd line of the op