Help making an auto extend formula macro!

[sombradeti]

Here is my problem. I basically have 2 Columns. Column 1 has a list of numbers in sequential order and Column 2 has the formula =IF(A2=A1+1, "yes") I want to make a macro that will automatically extend that formula down as I add new rows in Column 1. I think this would require an Event Macro but I am unsure how to make it. Is this possible?

 

[Abgar]

Hi there,

It's a very crude method, but you can copy the below formula all the way ot the end of column 2, and it will appear as "hidden" unless the result is true:

=IF(A2=A1+1,"Yes","")

I'm sure someone will have a better way of doing so though

 

[sombradeti]

That would almost work except i need it to also say "false" if the formula is false =IF(A2=A1+1,"Yes","False"). I kind of know what i need to tell it to do I just don't know the exact way to do it. If I can tell it to apply the formula and loop down until there is no data in the cell next to the formula in Column A it will accomplish what I'm trying to do.

 

[Abgar]

Hi Sombradeti,

Here is a macro that will do what you want.

Sub Formula()
Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
        Range("B2:B" & LastRow).Formula = "=IF(RC[-1]=R[-1]C[-1]+1,""Yes"",""False"")"
End Sub

I'm not sure how to implement the Change Event, but at least this will get you going to start with.

Cheers

 

[taigovinda]

If you want to hook the code from post #3 to a change event, you could do it like this (right-click the worksheet, View Code and paste in this code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long

'don't do anything unless column A was specifically changed
'if calculation of existing formulas causes change in column A, macro still exits and does not do anything
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

'prevent the macro from triggering itself (this macro changes the sheet)
Application.EnableEvents = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
        Range("B2:B" & LastRow).Formula = "=IF(RC[-1]=R[-1]C[-1]+1,""Yes"",""False"")"
're-enable events so they can trigger things after this macro is done
Application.EnableEvents = True

End Sub

 

[Abgar]

Hi Taigovinda,

Thanks for that - i'd never had known anything about change events - that will be handy for myself to keep

Do you know if there is a way to add a Change event into a traditional macro?

Cheers

 

[taigovinda]

You can hook up any macro you want to a change event.

Try going to the VB Editor (Alt+F11) and off to the left is your Project Explorer (control+R if you cannot see it).

It will have a 'project' for each book (e.g., book1) and within each project will be:
- microsoft excel objects
- modules (if you have added any modules in the book)

You put event code onto the microsoft excel objects - either ThisWorkbook or one of the sheets - try double-clicking any of the objects and their code window opens up.

At the top of the code window are two dropdowns. You can select workbook or worksheet from the one on the left. Then, based on that, you can select any number of events from the dropdown on the right and the VBE will create an opening and closing line for the event. As an example, within workbook events there is a worksheet_change event that will fire any time you make a change on any worksheet in the book; within worksheet events there is a change event that will fire any time you make a change on that particular sheet..

All you need to do is type your code in there that you want to fire - or if you want it to fire an existing macro in a module in the same book, just type in the name of the macro.

There are lots of better explanations out there on the net... try looking for worksheet events or workbook events.

Tai

 

[Abgar]

That's a great help for me personally

Thanks so much

 

[sombradeti]

Thanks a bunch guys those examples really helped.