Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy only to cells where there is data
Hello:
I have created a simple macro that, among other things, places a formula in I2 that subtracts H2 from G2. The macro, then, copies this formula down the entire column of I and formats column I to be "values" instead of "formulas". That's all well and good. But I only want the macro to conduct this sort of copying and formatting of column I only where there is data in column H. I mean, if the data in the spreadsheet does not go past row 131, then I do not want any data in column I that is beyond I131. Could someone please review my macro below and let me know how I can modify the VBA code to accomplish this? Thanks! childofthe1980s Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Need" Columns("I:I").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy only to cells where there is data
Hi,
Try this Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2:I" & lastrow).Copy Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "childofthe1980s" wrote: Hello: I have created a simple macro that, among other things, places a formula in I2 that subtracts H2 from G2. The macro, then, copies this formula down the entire column of I and formats column I to be "values" instead of "formulas". That's all well and good. But I only want the macro to conduct this sort of copying and formatting of column I only where there is data in column H. I mean, if the data in the spreadsheet does not go past row 131, then I do not want any data in column I that is beyond I131. Could someone please review my macro below and let me know how I can modify the VBA code to accomplish this? Thanks! childofthe1980s Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Need" Columns("I:I").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy only to cells where there is data
Better
Sub nn() Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row With Range("I2:I" & lastrow) .FormulaR1C1 = "=RC[-2]-RC[-1]" .Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2:I" & lastrow).Copy Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "childofthe1980s" wrote: Hello: I have created a simple macro that, among other things, places a formula in I2 that subtracts H2 from G2. The macro, then, copies this formula down the entire column of I and formats column I to be "values" instead of "formulas". That's all well and good. But I only want the macro to conduct this sort of copying and formatting of column I only where there is data in column H. I mean, if the data in the spreadsheet does not go past row 131, then I do not want any data in column I that is beyond I131. Could someone please review my macro below and let me know how I can modify the VBA code to accomplish this? Thanks! childofthe1980s Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Need" Columns("I:I").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy only to cells where there is data
This worked perfectly, Mike! Thank you!
If I have this issue in the future, what are some good VBA "buzzwords" to put into my programming to tell it to only copy data to the last record where there is data? childofthe1980s "Mike H" wrote: Better Sub nn() Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row With Range("I2:I" & lastrow) .FormulaR1C1 = "=RC[-2]-RC[-1]" .Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2:I" & lastrow).Copy Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "childofthe1980s" wrote: Hello: I have created a simple macro that, among other things, places a formula in I2 that subtracts H2 from G2. The macro, then, copies this formula down the entire column of I and formats column I to be "values" instead of "formulas". That's all well and good. But I only want the macro to conduct this sort of copying and formatting of column I only where there is data in column H. I mean, if the data in the spreadsheet does not go past row 131, then I do not want any data in column I that is beyond I131. Could someone please review my macro below and let me know how I can modify the VBA code to accomplish this? Thanks! childofthe1980s Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Need" Columns("I:I").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy only to cells where there is data
Hi,
It's a vey common question and there are several way of finding the last row including the one I used lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row Have a lookhere for som other methods, it also tells you seme methods for use on a worksheet http://www.mvps.org/dmcritchie/excel/lastcell.htm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "childofthe1980s" wrote: This worked perfectly, Mike! Thank you! If I have this issue in the future, what are some good VBA "buzzwords" to put into my programming to tell it to only copy data to the last record where there is data? childofthe1980s "Mike H" wrote: Better Sub nn() Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row With Range("I2:I" & lastrow) .FormulaR1C1 = "=RC[-2]-RC[-1]" .Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this Range("I1").Value = "Need" lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2:I" & lastrow).Copy Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "childofthe1980s" wrote: Hello: I have created a simple macro that, among other things, places a formula in I2 that subtracts H2 from G2. The macro, then, copies this formula down the entire column of I and formats column I to be "values" instead of "formulas". That's all well and good. But I only want the macro to conduct this sort of copying and formatting of column I only where there is data in column H. I mean, if the data in the spreadsheet does not go past row 131, then I do not want any data in column I that is beyond I131. Could someone please review my macro below and let me know how I can modify the VBA code to accomplish this? Thanks! childofthe1980s Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Range("I1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Need" Columns("I:I").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY DATA FROM 2 CELLS | Excel Programming | |||
How to copy data from merged cells to their individual cells? | Excel Programming | |||
how to copy cells data in every 7th row? | Excel Programming | |||
Copy data into cells until changes trigger new copy | Excel Programming | |||
How do I copy data (word) into respective cells when the data bei. | New Users to Excel |