| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy value problem... if "" then 0
Hi, the code below will copy the ActiveCell.Offset(0, 10).value... but
if ActiveCell.Offset(0, 10).value has nothing in it, I need it to paste a 0 instead of nothing... ActiveCell.Offset(0, 10).Copy Sheets("Datasheet").Range("I65536").End(xlUp).Offs et(1, 0).PasteSpecial Paste:=xlPasteValues Can anybody help please... ste |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy value problem... if "" then 0
Ste
No need to copy and paste, just assign the value directly Sub CopyData() Dim dVal As Double dVal = IIf(IsEmpty(ActiveCell.Offset(0, 10).Value), 0 _ , ActiveCell.Offset(0, 10).Value) Sheets("Datasheet").Range("I65536").End(xlUp).Offs et(1, 0).Value = dVal End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "ste mac" wrote in message om... Hi, the code below will copy the ActiveCell.Offset(0, 10).value... but if ActiveCell.Offset(0, 10).value has nothing in it, I need it to paste a 0 instead of nothing... ActiveCell.Offset(0, 10).Copy Sheets("Datasheet").Range("I65536").End(xlUp).Offs et(1, 0).PasteSpecial Paste:=xlPasteValues Can anybody help please... ste |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy value problem... if "" then 0
With Sheets("Datasheet").Range("I65536").End(xlUp).Offs et(1,0)
If Activecell.Offset(0,10).Value = "" Then ..Value = 0 Else ActiveCell.Offset(0, 10).Copy ..PasteSpecial Paste:=xlPasteValues End If -- HTH RP (remove nothere from the email address if mailing direct) "ste mac" wrote in message om... Hi, the code below will copy the ActiveCell.Offset(0, 10).value... but if ActiveCell.Offset(0, 10).value has nothing in it, I need it to paste a 0 instead of nothing... ActiveCell.Offset(0, 10).Copy Sheets("Datasheet").Range("I65536").End(xlUp).Offs et(1, 0).PasteSpecial Paste:=xlPasteValues Can anybody help please... ste |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy value problem... if "" then 0
Many Thanks to both Nick and Bob, I needed pointing in the right
direction...cheers... ste |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
| Shortcut to switch from "fill down" to "copy" with mouse drag | Excel Discussion (Misc queries) | |||
| problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
| Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming | |||
Linear Mode
