Hello

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:39 PM GMT

Question

QUESTION: hi i have anfile and i want to do somethings but i think it's impossible by formula.. so if i get VBA it's really good for me so my file is like that columnA, A2:A20 Date (same date 2/2/2010 for A2:A20) ColumnB, B2:B20 (serial number but not sure start number like 120,121,122,123.............) columnC,C2:C20 ( cost/ amount) columnD,D2:D20(person name) columnE, E2:E20(another amount) in this case i want...... i want insert new row if row has somethings like (between A1 to A2 new row,betweenA2 to A3 ..........until A20...put one by one new raw,every row downside. that mean each row down should be another empty row. until this step i can do also. after this i got problem the cell value also should be change like that when we insert new row A2:A40 (this double because the new row) should be same date B2:B40 ( this should be same Above cell like B2=120/B3=120,B4=121/B5 also 121........ until B40 C2:C40 ( the new cell cost is fix like all new cell value 110 (C2...C3=110,C4 .....,C5=110................. D2:D40 (Same above cell copy) if D2=X,D3=X,D4=Y,D5=y........... E2:E20 (this one same above copy but show - value like E2=100,E3=-100,E4=200,E5=-200...................... this really difficult for me please help me i really happy if somebody help me...n i hope...................

ANSWER: You can do this in ONE LINE OF CODE!:

After you've inserted the blank rows: Sub FillInRest()

  Range("A1:E40").SpecialCells(xlcelltypeblanks).formulaR1C1 = "=r[-1]c"

End Sub

---------- FOLLOW-UP ----------

QUESTION: this is not answer of my question

Answer

Then I don't understand your question, unless you're only concerned about column E (WHich I missed the first time): Sub FillInRest()

 Range("A1:D40").SpecialCells(xlcelltypeblanks).formulaR1C1 = "=r[-1]c"
 Range("E1:E40").SpecialCells(xlcelltypeblanks).formulaR1C1 = "=-r[-1]c"

End Sub

Advertisement

©2021 eLuminary LLC. All rights reserved.