If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
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. |
#2
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. |
#3
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. . |
#5
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. . . |
#6
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
Looks like a typo in the constant xldown.
Use have a 1 (one) instead of l (lowercase L) wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. . . -- Cheers Andy http://www.andypope.info |
#7
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
Yep, I put the "L" and it works... Thanks to all.
-----Original Message----- Looks like a typo in the constant xldown. Use have a 1 (one) instead of l (lowercase L) wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. . . -- Cheers Andy http://www.andypope.info . |
#8
|
|||
|
|||
FILL CELLS = VARIABLE # OF OTHER CELLS (WITH MACRO)
Good catch. I need to get new glasses.
- Jon Andy Pope wrote: Looks like a typo in the constant xldown. Use have a 1 (one) instead of l (lowercase L) wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: 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. . . |
Thread Tools | |
Display Modes | |
|
|