Run VBA when any cell within range <>=""

[p4nny]

Hi

Basically, I'm trying to run code when a value is entered in range("A:A"). The code will repopulate specific cells with formulae if the user inserts a new row and enters a value into Range("A:A")

I've searched for an event for INSERT ROW but this has been unsuccessful

Much appreciated

 

[Tim_Excel_]

If you determine the amount of rows when opening the WB with

k=sh.Range("A1048576").end(xlUp).row

You can add another piece of code to a Worksheet_Change event

lrow=sh.Range("A1048576").end(xlUp).row
If lrow <> k Then
'do code
k = lrow 'this resets the number of rows so the code won't run every time something changes unless you insert a new row
End if

 

[p4nny]

Hi

Thanks for the reply

When I enter the first piece of code as 'Workbook Open' event, I get the error message 'Object Required'

Much appreciated

Private Sub Workbook_Open()


k = Sh.Range("A1048576").End(xlUp).Row


End Sub

 

[Tim_Excel_]

You'll have to define Sh as your worksheet or simply use Worksheet("NAME") instead

 

[p4nny]

thanks for coming back to me. I should have spotted that!

My code is as follows entered in 'ThisWorkbook'

Private Sub Workbook_Open()


k = Worksheets("Action plan").Range("A1048576").End(xlUp).Row


End Sub

In the specific tab, code is:

Private Sub Worksheet_Change(ByVal Target As Range) 
lrow = Worksheets("Action plan").Range("A1048576").End(xlUp).Row
If lrow <> k Then
Worksheets("action plan").Range("ba1") = "hello"


k = lrow 'this resets the number of rows so the code won't run every time something changes unless you insert a new row
End If


End Sub

I get the following error message when inserting a row 'Method 'Range' of object _Worksheet failed. I've simply put "Hello" in the code as a test, so in my understanding "Hello" would appear in cell BA1 when a row is inserted

Many thanks

 

[Tim_Excel_]

The Range error tells you your range code is incorrect. Change "Range("ba1") to Range("BA1")

 

[p4nny]

thanks again!!

the same error appears now and highlights:

lrow = Worksheets("Action plan").Range("A1048576").End(xlUp).Row

Is there anything you can suggest?

many thanks

 

[Tim_Excel_]

The code works perfectly fine on my end. My bet is that your reference is wrong. Please check your references and spelling before asking questions. Note that everything between "" is extremely specific

 

[p4nny]

Thanks. I've tried all you've suggested but without success. I'm looking for an alternate solution as maybe i wasn't clear in my original requirement

appreciate the help

 

[Fluff]

Hia
How about

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target.Count <> 1 And Target.Column = 1 Then
        Target.Offset(, 4).FormulaR1C1 = "=rc[-4]+rc[-3]"
    End If
    
End Sub

This will insert a formula into column E when a change is made to column A