Help with a Macro to find last column of data and paste formulas

[matador_24]

Hello guys,

I have this table:


Windows("RECONCILIATION WORKING FILE.xlsx").Activate
table.JPG



I am building a macro where I paste data (columns A to E) to a new excel file (thus, columns F and G are blank) and then:

1. I need to go to the last column with data in row 2 (in this case cell F2) and I add the text: 'New Account'
2. Then I need to go down one row and paste a formula (in this case cell F3): =IF(OR(LEFT(B3,5)="10099",LEFT(B3,5)="10000"),LEFT(B3,5)&"-"&RIGHT(B3,3),LEFT(B3,2)&"-"&MID(B3,11,7)&"-"&LEFT(B3,5))
3. Then I need to copy the formula until the last row of data
4. Then I need to do the same thing for colum G (another text and another formula).

So far I have this:

Please Login or Register  to view this content.

but it doesn't work, it shows a 'compilation error' in the formula part of the formula (''10099'') and it stops.... also, I am not sure it is ok.....

please could anyone help me out to finish the code?

thanks!

[StephenR]

One thing you have to do in VBA is double-up the quotes:

Please Login or Register  to view this content.

There may be other issues. A workbook is more useful than a picture.

[Marcol]

I suspect that you are wanting to add a column to your table, at least initially.

Give this a try, it isn't all necessarily required , but it should show you where to go.

Please Login or Register  to view this content.

[matador_24]

Thanks a lot guys, that is exactly what I was looking for!

I really appreciate the help of both!

[matador_24]

Hello, just one question.... after the result of the formula, I have to 'auto shape' the column with the formula because the result is long..... what can I add at the end of the code to do so????
and also, what can I add so that the fill of the 'New account' cell is in blue? (any blue color)

thanks

[matador_24]

A little of help here please

[StephenR]

Try this:

Please Login or Register  to view this content.

[matador_24]

thanks a lot guys!