Consulting

Results 1 to 3 of 3

Thread: Referencing a range into formula: explanation

  1. #1
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location

    Referencing a range into formula: explanation

    Hello everyone,

    I was trying to get a range into a formula:

    Cells(1, 11).Formula = "=COUNTIF(" & Countif.Address & ",J1)"
    I have found the solution while browsing on google.
    However, I don't understand why the typo has to be as herein-above.

    Would you have some explanation about the "{Space}" and the "&"? I tried to find some explanations online but didn't find anything...

    Thanks in advance for your time and help.
    Edmond

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    .Formula is looking for a string that equates to a 'worksheet formula'

    Assuming that Countif is a range variable, say column A, then it has a .Address of "A:A"

    The string that is generated is

    "=COUNTIF(A:A,J1)"

    and so K1 has the formula

    =COUNTIF(A:A,J1) in it

    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    As usual, thanks a lot Paul!
    Edmond

Posting Permissions

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