User input controlled data

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
106
Hi,

I am not sure if this is possible but I think if it is I might have to use VBA! Due to my lack of knowledge this will be a learning curve :eek:

This is what I am hoping is possible. When you enter a number into cell A1 it automatically fills out data below depending on the value.

For example, if the number entered is 5 then cell B1 will have "2014", cell B3 will have "2015" and so on until cell B9 has "2018". I want a gap between cells which is why it skips a cell. if the user puts in 20 then it will follow the same pattern but end in cell B39 with a value on 2033.

I'd then like to put a formula in cell C1, =SUM(C1*0.06+C1), and copy that down as far as the data in the B column went. Again missing a cell and matching the pattern in the B column.

All of this I am hoping is possible from a simple input of a number into cell A1.

If this isn't possible can anyone suggest an alternative way to achieve this?

Many thanks
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to MrExcel.

You can't have the formula:

=SUM(C1*0.06+C1),

in C1. That will create a circular reference.
 
Upvote 0
Welcome to MrExcel.

You can't have the formula:

=SUM(C1*0.06+C1),

in C1. That will create a circular reference.
Hi Andrew,

Thank you you pointing that out. A typo on my part, the formula should go in C3.

Do you have any idea on if the rest is possible?
 
Upvote 0
Try this in the module for the Worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    With Me
        .Columns("B:C").ClearContents
        .Range("B1").Value = 2014
        For i = 3 To Target.Value * 2 Step 2
            .Range("B" & i).Value = .Range("B" & i - 2).Value + 1
            .Range("C" & i).FormulaR1C1 = "=R[-2]C*1.06"
        Next i
    End With
    Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
Try this in the module for the Worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    With Me
        .Columns("B:C").ClearContents
        .Range("B1").Value = 2014
        For i = 3 To Target.Value * 2 Step 2
            .Range("B" & i).Value = .Range("B" & i - 2).Value + 1
            .Range("C" & i).FormulaR1C1 = "=R[-2]C*1.06"
        Next i
    End With
    Application.EnableEvents = True
End Sub
Hi Andrew,

This works perfectly! Thank you so much.

I have tweaked it slightly to fit my spreadsheet and I am struggling to understand how you insert the formula as this line of code is very complicated.

For example in column B for the year 2015 I would need the sum to be =SUM(B20*N5+B20)

This is my code from what you gave me:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    With Me
        .Range("B20:J30").ClearContents
        .Range("B20").Value = 2014
        .Range("C20:J20").Value = 0
        For i = 3 To Target.Value * 2 Step 2
            .Range("B" & i + 19).Value = .Range("B" & (i + 19) - 2).Value + 1
            .Range("C" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("D" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("E" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("F" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("G" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("H" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("I" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("J" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
        Next i
    End With
    Application.EnableEvents = True
End Sub

I just can't figure out how the code works to amend a formula and I would really like to get my head around it. The specific part that i am struggling with is:

Code:
.FormulaR1C1 = "=R[-2]C*1.06"</pre>

I appreciate your help and advice a lot!
 
Upvote 0
I have made a little more progress and now I understand what the R1C1 means and what it references.

So now I have got the following code working:

Code:
.Range("C" & i + 19).FormulaR1C1 = "=R[-2]C*0.06+R[-2]C"

The only thing I have left to do is put a cell reference in instead of 0.06 so that the formula works when the value is changed. This is the part I am now stuck on!

So my question is how do I change 0.06 in the formula to reference cell D4?

I have tried using R4C4 but this isn't working.

Any help is appreciated :)
 
Upvote 0
If the code errored at some point in your testing, events would have been disabled. Is that what you meant by not working - ie nothing happened? You can quickly reenable events by typing:

Application.EnableEvents = True

in the Immediate window and pressing Enter.
 
Upvote 0
If the code errored at some point in your testing, events would have been disabled. Is that what you meant by not working - ie nothing happened? You can quickly reenable events by typing:

Application.EnableEvents = True

in the Immediate window and pressing Enter.
Yes that worked! Thank you again :)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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