Solved: Copy Paste except no background

[slamet Harto]

Hi guys,

Kindly advise what I'm doing wrong with the following code:

For instance: Range I4 has a formula to be copied to range I5:I10.
However, some of cells in range I5:I10 has colour / background that no need to paste.

Thank you for your assistance.
Rgds, Harto

Here is what I've so far:
[VBA]Sub PasteNoColour()

Dim RangeBiruasin As Range
Dim xRowsCount As Long
Dim r As Long

On Error Resume Next
ActiveCell.Copy 'copy cell I4
Set RangeBiruasin = Range("I5:I10") ' Application.InputBox( _
'Prompt:="Select Range to be processed.!", _
'Title:="Copy Paste formula except colour", _
'Default:=Selection.Address, Type:=8)


xRowsCount = RangeBiruasin.Rows.Count
Application.Calculation = xlCalculationManual



For r = xRowsCount To 1 Step -1 'pls advice me whether we need to use "STEP-1" ?

If RangeBiruasin(r, 1).Interior.ColorIndex <> 34 Then


RangeBiruasin(r, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Application.StatusBar = " . . . . . . . . . . . . . . . . . . Scanning Row:" & Str(r)
Next r

Application.Calculation = xlCalculationAutomatic
pplication.StatusBar = ""
End Sub
[/VBA]

[mikerickson]

[VBA]Range("I5:I10").FormulaR1C1 = Range("I4").FormulaR1C1[/VBA]

[GTO]

...For instance: Range I4 has a formula to be copied to range I5:I10.
...
However, some of cells in range I5:I10 has colour / background that no need to paste...

Hi Harto :-)

Just want to make sure I (being rather thick-headed and all) understand.

For each cell in the range, IF it has colorindex 34, then skip. Otherwise copy the formula from I4. Right?

Mark

[slamet Harto]

Hi all

Thanks for your quick response.

Mark,
Yes, you are right.

[GTO]

Okay, I see two little issues. The first is that when I step-thru it, when you change Calculation to Manual, the CutCopyMode appears to go to False (ie - there's no longer anything copied; like hitting the Esc button).

Anyways, the next thing is this:

Let's say the Value of I3 is 4.
Let's say the formula in I4 is: =I3 + 2

Now normally, if you just copied down, I5 would be: "I4 + 2", I6 would be: "=I5 + 2", and so on. So the cells would end up have the values (top to bottom) 6,8,10,12....

But with this way of skipping the formula, the cell just below the light turquise cell, would end up having the value of 2, as it is adding to zero (the value of the blank cell). Is that okay?

Mark

[GTO]

Sorry, if I had the brains God gave cats, I would have just said this:

Comment out the line (line 14 by my count) that sets Calculation to Manual, and then run it. You will see what I mean.

Mark

[slamet Harto]

Hi mark,

Brilliant!! set calculation xl manual is caused.

Thanks for spent your time.
Best, harto

[GTO]

You bet Harto, happy to "meet" as well.

Mark