none
Excel vba same formula to all worksheets in range RRS feed

  • Question

  • Using VBA,   Is there a way to copy this formula =IF($B5="","","'"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)&" '! "&ADDRESS(ROW(),COLUMN()))  to all worksheets, in a range from $E4 to $F100. Without disturbing the commented cells or the fact some cells maybe merged?

    Thank you


    heads up

    Wednesday, October 8, 2014 1:02 PM

All replies

  • Hello,

    You mean something like this:

    Dim ws As Worksheet, c As Range

        
        For Each ws In ThisWorkbook.Worksheets
            For Each c In ws.Range("E4:F100")
                c.Formula = "=IF($B5="""","""","""'"&MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,20)&"" '! ""&ADDRESS(ROW(),COLUMN())) "
            Next
        Next

    just hope the quotes are correct ;

    Wednesday, October 8, 2014 2:40 PM
  • Your a smart and funny guy!!.... My quotes work in the excel workbook but not vba. (how did  you know that LOL!) I'm wanting a solution that places in every cell (within the range) , the formula that (grabs the worksheet name and the cell address, based on the ciriteia of Column "B" cells content of the same row, not empty , else skip to next row. I also want to preserve the formatting of the Cell, some cells are merged and your routine currently removes the merge.  All of this to be used later when I consolidate sheets. Make sense?


    heads up

    Wednesday, October 8, 2014 4:47 PM
  • Hi,

    >>I also want to preserve the formatting of the Cell, some cells are merged and your routine currently removes the merge. <<

    What do you mean by "your routine currently removes the merge"?

    I think the code provided by @Wouter Defour won't remove the merge property of cells. It works to set the formula for every cells in the range ("E4:F100") of every worksheet.

    Does the code above can help you? If not, would you mind provide more details such as your sample workbook to help us understand?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 9, 2014 8:46 AM
    Moderator