-1

If I have a cell (A1) value is "2" (set value)
& another cell (A2) value is "4" (number of cells in 1 column i want the set value to appear in)

How do I return the values in B1:B4 to all be "2"?

1
  • Do you have a formula in B1:B4 now? What is it?
    – datatoo
    Aug 29, 2014 at 17:40

1 Answer 1

2

Updated, VBA answer

Under the Developer tab (if you don't have it, go to Options, Customize Ribbon, and check it) insert a Command Button from the ActiveX section.

Place the button, right click, View Code. Type this:

Private Sub CommandButton1_Click()
  Call Rawr
End Sub

Then, still in the VB environment, click Insert, Module. In your new module:

Sub Rawr()

Dim i, x, cap As Integer

cap = [A2]
x = [A1]

[B1].Select

For i = 0 To cap - 1
 ActiveCell.Offset(i, 0).Value = x
Next i

End Sub

You'll probably want some additional code to clear column B for when you have new parameters. Add another button with this code:

Columns("B:B").Select
Selection.ClearContents

Original Answer

It sounds like you're asking how to make the first x rows in column B equal to y, where x is A2 and y is A1 - is that right?

There's isn't a way to populate empty cells with values like this with a formula - you'd likely want to use a VBA approach.

If you're comfortable with having formulas in column B that simply look blank until needed, place this in B1, and fill it down:

=IF(ROW()<=$A$2,$A$1,"")
3
  • Yeah, I couldn't really see any fx solution w/o these blank cells. Appreciate the help though. VBA it is. Thanks.
    – Will S
    Aug 29, 2014 at 18:18
  • Wasn't sure if you had a VBA solution already (and were searching for alternates) or were in need of one. Aug 29, 2014 at 18:37
  • Got one, always searching for alternates though. Thanks!
    – Will S
    Aug 29, 2014 at 18:42

Not the answer you're looking for? Browse other questions tagged .