Skipping cells that contain value

[barbs706]

Hi all.

I currently have a workbook that if column B gets edited/activated, the sheet calls a macro that updates the formulas in column D
This is the current code, which suites are needs:

Range("D9").Select    
Selection.AutoFill Destination:=Range("D9:D1993"), Type:=xlFillValues
Range("D9:D1993").Select

The actual formula it copies down is =Q9+X9, =Q10+X10 and so on, all the way to =Q1993+X1993

I now have a requirement that I need to put the letters "inc" into column D. However when I type this into the cell then activate a random cell in column B, it calls the macro and overwrites all the "inc"

Is there a way to exclude the cells in column D that contain "inc" from being overwritten when the macro is called?

Many thanks.

 

[Norie]

How is the macro being called?

 

[barbs706]

Hi Norie

Private Sub Worksheet_Change(ByVal Target As Range)


Application.ScreenUpdating = False
If Target.Column = 2 Then
Call UpdateFormulas
Application.ScreenUpdating = True
End If


End Sub

Many thanks.

 

[Rick Rothstein]

I currently have a workbook that if column B gets edited/activated, the sheet calls a macro that updates the formulas in column D
This is the current code, which suites are needs:

Range("D9").Select    
Selection.AutoFill Destination:=Range("D9:D1993"), Type:=xlFillValues
Range("D9:D1993").Select

The actual formula it copies down is =Q9+X9, =Q10+X10 and so on, all the way to =Q1993+X1993

Putting aside the question of Inc in cells for now, I am curious... if you have formulas in cells D9:D1993 already, why do you "update" them? A formula in a cell updates automatically when any of the cells it depends on changes (unless you have turned off automatic calculations)... so I don't understand why you are recopying the same formulas over themselves any time you change a cell in Column B.

 

[barbs706]

Hi Rick.

I believe this was so that when a new row is added, the formulas are automatically added.

The workbook is getting old now and more than one person has been adding bits and pieces over the years, so the code that is used could probably be improved quite a lot.
That's for a different day though!

 

[Rick Rothstein]

Hi Rick.

I believe this was so that when a new row is added, the formulas are automatically added.

I am not sure how that would apply given what you showed us... the range for your code is fixed at D9:D1993 and won't change if new rows are added... or are you saying you modify the code every time someone adds a row?

 

[barbs706]

Extra rows are normally only added within the first 200 so the code will still put the formulas in the added rows. And we only ever have to add more than 10 rows. We rarely use any more than 1000 rows for each workbook (It's a template by the way!)

I did think about trimming down the rows but it doesn't seem to cause any slowdowns or take up a lot more space.

Thanks.

 

[Rick Rothstein]

Extra rows are normally only added within the first 200 so the code will still put the formulas in the added rows. And we only ever have to add more than 10 rows. We rarely use any more than 1000 rows for each workbook (It's a template by the way!)

So you actually only need to put those formulas into blank cells only, correct? If so, this should work for you...

Sub UpdateFormulas()
  On Error GoTo NoBlanks
  Range("D9:D1993").SpecialCells(xlBlanks).FormulaR1C1 = Range("D9").FormulaR1C1
NoBlanks:
End Sub