How to Insert text/formula in to cells based on the text in the cell beside

Hi,

I have got a list of data in Sheet 2 that references cells in Sheet 1. I am using a couple of macros to insert rows and edit font/row sizes, etc. into Sheet 2.

I now need to insert the formula "=IF(MOD(ROW(),1)=0,INDEX(Sheet1!$C$4:$C$49271,ROW()/2),"")" into the cell in column A of every newly created row.

I will then need to insert a few new rules based on some other criteria. As this will then change the data that the above formula will reference, I need to copy and paste this information as a value before I do the next step.

The macro I am using to create the rows is below with the cell I am trying to add this formula to highlighted in red. From what I can figure out there is no way to add the formula directly within this macro and I will need to run a new one, but I am honestly completely lost. Can anyone shed some light on what I should do?

Please Login or Register  to view this content.

Does this not work?

Please Login or Register  to view this content.

Notice the doubled-up quotation marks within the formula string. That's necessary to avoid the Formula = "..." string from being terminated prematurely.

One other thing to note is that this needs to be in a standard module, not the Sheet1 or Sheet2 code module, otherwise it won't be able to amend a worksheet other than itself.

If that doesn't help, it might be best to refer to the yellow band at the top of the page and attach a de-sensitised version of your workbook so we can see what's going on.

HTH
Tim

Use

Please Login or Register  to view this content.

Jinx

Yes this is exactly what I was trying to do, my inexperience with VBA is definitely showing.

My 2nd issue is copying and pasting these cells as a value. I can manually just copy and paste all of column A to get a workable result for what I am doing but am hoping to find something that will only copy and paste those specific cells. Any help appreciated.


(also is there a simple way to input a bottom border into these cells e.g. .Border = Bottom or something to that effect)

How about this for the border...

Please Login or Register  to view this content.

I'm not quite sure what you want to copy/paste and where to. In general, you could perhaps adapt something like this:

Please Login or Register  to view this content.

Does that help move things along?

Tim

Yes that is exactly the border info I was looking for, thank you.

The copy and pasting is literally into the same cells the inputted formula is already in. I just need to change it into a value so that when I run my next macro (that adds more rows) the information that was brought in form Sheet1 with that formula doesn't change.

Perhaps something like this:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.


If you want to convert the formula you have just inserted, use:

Please Login or Register  to view this content.

You can overwrite a formula with the resulting value like this:

Please Login or Register  to view this content.

Easy peasy!

Tim

You're quite welcome, and thanks for rep.

Tim