Copying a formula to dynamic range

[2vao]

hi all,

I checked all the threads that could be altered a bit to get what I want, but I believe because of my very limited knowledge of VBA, I couldn't reach anywhere. Here is what I have:

- Formula in cell D3 that counts the number of records based on the value of cell A11.
- An array formula in cell B11 that I want to copy down to a dynamic range starting from Cell B12: the number of rows will depend on the value of D3 each time A11 changes.

What I want to happen each time A11 changes is:
-A code that clears range B12:B
-Copy B11 to Range B12:B(12+whatever the value of D3 is)

Kindly help, will appreciate it very much.
Thanks

 

[JoeMo]

Does A11 have a formula that changes the value of the cell when the worksheet calculates, or is A11 changed by a user's manual entry?

 

[2vao]

Hi Joe,

A11 is an user manual entry

Thanks

 

[JoeMo]

Here's a worksheet_change event code, After you install it following the steps below, it will automatically do what you requested whenever a change is made to cell A11, provided the value in D3 is more than 11.

To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11")) Is Nothing Then
    Application.EnableEvents = False
    If [D3] > 11 Then
        Range("B12:B" & [D3]).ClearContents
        Range("B11:B" & [D3]).FillDown
    End If
    Application.EnableEvents = True
End If
End Sub

 

[2vao]

Hi Joe,

First I apologize for my late response; Thanks for the code, I was wondering why the value of D3 has to be >11.
What I am trying to get is that if for example D3=3 then, copy B11 to range B12:B13 i.e 3 rows including B11.
If D3=5 Then, copy B11 to range B12:B16 i.e 5 rows including B11.

I tried to just change: If [D3]>0 then the code but it didn't run.

I appreciate your help.

Many thanks.

 

[JoeMo]

Thanks for the clarification. See if this does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11")) Is Nothing Then
    Application.EnableEvents = False
    If [D3] > 0 Then
        Range("B12:B" & 11 + [D3]).ClearContents
        Range("B11:B" & 11 + [D3]).FillDown
    End If
    Application.EnableEvents = True
End If
End Sub

 

[2vao]

Hi Joe,

I am not sure what I am doing wrong, I literally copied and pasted the code, but for some reasons nothing happened. Not even an error message. I am using Excel 2016 for Mac

best regards.

 

[JoeMo]

Hi Joe,

I am not sure what I am doing wrong, I literally copied and pasted the code, but for some reasons nothing happened. Not even an error message. I am using Excel 2016 for Mac

best regards.

If you followed the steps in post #4 to install the code in the correct place and nothing happens when you manually change A11, then you may have inadvertently disabled events.

To enable events again do the following:
1. Open the VB Editor using alt+F11 keys
2. Open the Immediate Window in the VB Editor using ctrl+g
3. In the Immediate Window type: Application.EnableEvents = True
4. Press Enter
Now make a change to cell A11 and the code should run.

 

[2vao]

Hi Joe,
With Excel 2016 Mac, the VBE opens through developer=>Visual Basic. I did make the changes as per your advice, still it did not run.
I tried to run it using the Run Macro command but I get prompted to create a new macro...I don't know how to proceed.

Thanks for your help.

 

[JoeMo]

Hi Joe,
With Excel 2016 Mac, the VBE opens through developer=>Visual Basic. I did make the changes as per your advice, still it did not run.
I tried to run it using the Run Macro command but I get prompted to create a new macro...I don't know how to proceed.

Thanks for your help.

I don't use a Mac so can only guess that you haven't placed the code correctly or events are still not enabled. Do you not see View Code on the context menu when you right-click the sheet tab?

EDIT: Place the code below in the same place you have the change event code. then select any cell on the sheet - do you get a pop-up message?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Events are Enabled"
End Sub