+ 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
    41

    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
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    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 2007,2010,365
    Posts
    44,086

    Re: VBA to combine Sheet & CellAddress

    Untested, but try:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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 2007,2010,365
    Posts
    44,086

    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
    41

    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
    41

    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 2007,2010,365
    Posts
    44,086

    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
    9,831

    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)

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2016, 10:09 AM
  2. Replies: 0
    Last Post: 10-12-2015, 10:02 AM
  3. Replies: 2
    Last Post: 06-11-2014, 11:14 AM
  4. [SOLVED] Transfer and combine data from 2 separate Sheet to 1 sheet
    By muddbog in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-10-2014, 01:51 PM
  5. [SOLVED] VBA code to combine data from two different sheet columns and paste new sheet and column?
    By Chris* in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2014, 03:15 AM
  6. [SOLVED] combine multi workbooks into one master workbook but I want to combine only sheet 3
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-31-2013, 12:22 AM
  7. Replies: 3
    Last Post: 11-09-2013, 02:38 PM

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