+ Reply to Thread
Results 1 to 8 of 8

VBA to combine Sheet & CellAddress

  1. #1
    Registered User
    Join Date
    08-29-2016
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    24

    VBA to combine Sheet & CellAddress

    Hi all

    I am trying to input a formula (=A!E15) into Range("B4")

    There is some issue with the VBA formula .

    PHP Code: 
    Sub Formula()

        
    Sheets("A").Select
        Range
    ("E" Rows.Count).End(xlUp).Select
        
        CellAddress 
    ActiveCell.Value
        Sheets
    ("Summary").Select
        Range
    ("B4").Formula "='A! & CellAddress'"

    End Sub 
    Kindly advise.
    Attached Files Attached Files
    Last edited by fantasylo; 06-10-2019 at 11:21 AM.

  2. #2
    Valued Forum Contributor KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    998

    Re: VBA to combine Sheet & CellAddress

    Please Login or Register  to view this content.
    If you want to block this address (like =A!$E$15) remove replace in last line.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,237

    Re: VBA to combine Sheet & CellAddress

    Untested, but try:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,237

    Re: VBA to combine Sheet & CellAddress

    Or, if not absolute:

    Untested, but try:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-29-2016
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    24

    Re: VBA to combine Sheet & CellAddress

    Hi Kokosek

    Thank you for your prompt reply. It works.

    Many thanks!

  6. #6
    Registered User
    Join Date
    08-29-2016
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    24

    Re: VBA to combine Sheet & CellAddress

    Hi TMS

    Thanks for your replies. They work as well.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,237

    Re: VBA to combine Sheet & CellAddress

    You're welcome. Thanks for the rep.


    You are better off avoiding selecting sheets and/or cells. It's best practice and faster.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    3,170

    Cool Hi ! Try this ‼

    Quote Originally Posted by fantasylo View Post
    Kindly advise.
    According to your attachment as a beginner starter :

    PHP Code: 
    Sub Formula()
             
    Dim Rg As Range
        
    For Each Rg In Sheet1.[B4:D4]
            
    Rg.Formula "=" Sheets(Rg(0).Value2).Cells(Rows.Count5).End(xlUp).Address(FalseFalse, , True)
        
    Next
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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