+ Reply to Thread
Results 1 to 8 of 8

VBA Write formula =GetCells(W:AF) across all worksheets

  1. #1
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    VBA Write formula =GetCells(W:AF) across all worksheets

    Hey I need to write formula = Getcells(W:AF) so that it fills down the whole sheet but stops when column A no longer has data. I want the formula to be inserted into column AG. I also would like the formula =len(AG) in each cell down the whole page for each worksheet. Does anyone know if this is possible? I have experimented but no success :/

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    Can you explain what you're trying to do in a bit more detail?

    Do the columns W-AF contain the data you want to write down the sheet? If so are you trying to write sequentially so, say, AG1 contains W1, AG2 contains X1, AG3 contains Y1, etc.?

    If so you could use in AG1:

    =IF(A1="","",INDEX(W:AF,INT((ROW(A1)-1)/10)+1,MOD(ROW(A1)-1,10)+1))

    And in AH1:

    =IF(AG1="","",LEN(AF1))

    And drag down as far as you need - both formula will stop returning values once column A is empty.

  3. #3
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    That is not what I want to do. I have this VBA code:

    Function GetCells(MyRange As Range)
    Dim Cell As Range
    For Each Cell In MyRange
    If Cell <> "" Then
    GetCells = GetCells & Cell.Value & " "
    End If
    Next Cell
    If GetCells <> "" Then GetCells = Left(GetCells, Len(GetCells) - 1)
    End Function
    Sub Concatenate()

    End Sub


    When I do the Get Cells formula, it concatenates the whole thing. I do not want to do this and drag down. I have hundreds of sheets. Is there not a quicker way?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    What exactly are you trying to do with GetCells?

    Do you really want it to concatenate 10,458,760 cells?
    If posting code please use code tags, see here.

  5. #5
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    No, for instance, I have: 123 456 789 in cells A, B, C. Then D contains 123 456 789
    Etc. For a lot of cells down the page

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    The formula you posted had a reference to W:AF - that's 10 columns with over 1 million cells per column.

    I'm pretty sure you don't want to go through all those cells.

    What do you actually want to do?

    By the way, when posting code please add code tags - see my signature for a link.

  7. #7
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    No, I only have a few rows in each document. I want it to only cover the cells I have data. To look at column A and know when to stop. It might only go down 8 rows. Does that make sense?

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: VBA Write formula =GetCells(W:AF) across all worksheets

    You're trying to do too much with one function.

    It's a fundamental law of formula and user-defined functions that they can not write a value back to the cell other than the one they're in, so you can't do it all in one go.

    Your GetCells function is fine, so all you need is a sub to write it to the cells you want:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1