FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

COL A has variable number of cells...
Want to fill equal number of COL B cells with the number
70...

EXAMPLE:
If COL A has 8 cells, want only 8 cells of COL B with 70.
I don't want CELL 9 thru CELL X to have any values.

NEED THE MACRO CODING PLEASE.
 
Manually: enter 70 in cell B1, then double click the little square at
the bottom right of the cell.

In code (assuming only column A has any values):

Sub FillNextToColA()
Dim rng As Range
Set rng = ActiveSheet.Cells(1, 1).CurrentRegion.Offset(0, 1)
rng.Value = 70
End Sub

- Jon
 
Absolutely perfect. One last update..
A has data.. B gets filled with 70..
Make col c all blank and col d fill with 90.

Can't figure the mechanism to bump past Col c.
The macro code is exactly what I need.
Thank you very very much.
 
A little refinement, then.

Sub FillNextToColA()
Dim rng As Range
Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, 1).End(xlDown))
rng.offset(0,1).Value = 70
rng.offset(0,3).Value = 90
End Sub

- Jon
 
I'm getting run time error 1004

It's crying about ..
Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, 1).End(x1down))

The arrow on error is pointing to the ACTIVESHEET line.
 
Looks like a typo in the constant xldown.

Use have a 1 (one) instead of l (lowercase L)

I'm getting run time error 1004

It's crying about ..
Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, 1).End(x1down))

The arrow on error is pointing to the ACTIVESHEET line.
Click to expand...

--

Cheers
Andy

http://www.andypope.info
 
Back
Top