CODEWITHSUNDEEP
excelvba
TNi
TNi

Reputation: 16692

Unknown Length Array into Cells?

This time, I have a cell holding a natural number, let's say 10. Starting from another cell and for the 9 columns to the right of it, I want to display the numbers 1 through 10. If my reference cell held 20, however, I want to start at the other cell and display the numbers 1 through 20 in 20 columns. I hope you get the gist.

Is there a good way to do this, first of all, without VBA? If not, is there a good way to do this with VBA? Is there some event I should catch on a cell which would allow me to then manually insert the values I want? My knowledge of Excel and VBA is not very good, so sorry if the question is an easy one.

Thank you all in advance!

Upvotes: 1

Views: 1326

Answers (3)

Lance Roberts
Lance Roberts

Reputation: 22842

You won't be able to use a UDF since values are changed in more than one cell. So the easiest way (with VBA) is probably with the Change event. If the value is in the named range "holdvalue" and you want to start the numbers at range "valueStart" then this will work:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = Range("holdvalue").Address Then
  For i = 1 To Range("holdvalue")
    Range("valueStart").Offset(0, i - 1) = i
  Next i
End If

End Sub

Note: This doesn't deal with clearing the values.

Upvotes: 2

Jon49
Jon49

Reputation: 4606

You can do this with formulas:

=IF(COLUMN()<$A$35+2,COLUMN()-1,"")

Where $A$35 is the reference cell and I started the "another cell" in column B. If you start at a later column you would need to adjust the +2 and -1 in the formula accordingly. Just drag out the formula to where you would expect the max number would be, you can also do a validation on the reference cell to make sure it is below the max number and a whole number.

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33175

Let's say that the cell with the number is A3 and the you want to start the series in F3. In F3, put this formula

 =IF(COLUMN()-5<=$A$3,COLUMN()-5,"")

Copy F3 and paste to the right (or fill right) for as many columns as you may ever need. If A3 will never be more than 100, then fill right for 106 columns. If you want to start in a column other than F, you'll need to change the -5 parts of the formula.

Upvotes: 3

Related Questions