Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a null value to keep format???
my table is as follows
A1 B1 001e 4-16-06 002e 1-21-06 002f 3-30-06 I have a formula to find values in my data table and paste them in a very specific order in another worksheet(AUTO_GRAPH) for 90+ entries: Dim Rng As Range, Rng1 As Range Dim rng2 As Range Application.Worksheets("NEW_WCS").Select Set Rng = Range("B1").EntireColumn.Find(What:="001E", LookAt:=xlWhole, LookIn:=xlValues) If Rng Is Nothing Then Exit Sub Else Set Rng1 = Rng Do While Rng1.Offset(1, 0).Value = "001E" Set Rng1 = Rng1.Offset(1, 0) Loop Set rng2 = Worksheets("AUTO_WATER_STACK").Cells(Rows.Count, 8).End(xlUp)(5) Range(Rng, Rng1).Offset(0, 11).Copy rng2.PasteSpecial xlPasteAll, Transpose:=True End If What I need is: if there is no value for "002e", I need the code to "paste" a blank (nothing) into the specified row and column identified in the code (rng2, 4 rows down from the previous line code so I can keep my format on the worksheet (AUTOGRAPH) which never changes. Any help is greatly appreciated! Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a null value to keep format???
Do you mean to just replace the Exit Sub by
rng2.Cells(4,1).Value = "" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jermmcd" wrote in message ... my table is as follows A1 B1 001e 4-16-06 002e 1-21-06 002f 3-30-06 I have a formula to find values in my data table and paste them in a very specific order in another worksheet(AUTO_GRAPH) for 90+ entries: Dim Rng As Range, Rng1 As Range Dim rng2 As Range Application.Worksheets("NEW_WCS").Select Set Rng = Range("B1").EntireColumn.Find(What:="001E", LookAt:=xlWhole, LookIn:=xlValues) If Rng Is Nothing Then Exit Sub Else Set Rng1 = Rng Do While Rng1.Offset(1, 0).Value = "001E" Set Rng1 = Rng1.Offset(1, 0) Loop Set rng2 = Worksheets("AUTO_WATER_STACK").Cells(Rows.Count, 8).End(xlUp)(5) Range(Rng, Rng1).Offset(0, 11).Copy rng2.PasteSpecial xlPasteAll, Transpose:=True End If What I need is: if there is no value for "002e", I need the code to "paste" a blank (nothing) into the specified row and column identified in the code (rng2, 4 rows down from the previous line code so I can keep my format on the worksheet (AUTOGRAPH) which never changes. Any help is greatly appreciated! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
Test for null - insert a row if isnull | Excel Discussion (Misc queries) | |||
Pivot table format changes if value of source data is null | Excel Discussion (Misc queries) | |||
Formulas (If linked data is not null, then insert "Complete" in ce | Excel Worksheet Functions | |||
Insert cell/format/text/fontsize and auto insert into header? | Excel Programming |