Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Set Cell Value Equal to Another using VBA

  1. #1
    Join Date
    19th March 2011
    Posts
    42

    Set Cell Value Equal to Another using VBA

    Hi all,

    I was trying to figure out how to set value for sheet1.range("F:X") equal to sheet2.range("A:T") with syntax:
    VB:
    sheet1.range(cells(4,6),cells(4,24))=sheet2.range(cells(2,2),cells(2,20)).value 
    
    
    The reason I'm using cells instead of range is the variable of row I'll need to pass to the code above.
    But getting nowhere other than the error message that keeps telling me range class failed sort of thing.

    What is proper syntax to get the result?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st December 2006
    Posts
    1,058

    Re: Set Cell Value Equal to Another using VBA

    Hi Guner,

    You can use variables in ranges, i.e.

    VB:
    Sub Macro2() 
        Dim lngRowSource As Long, _ 
        lngRowDestin As Long 
         
        lngRowSource = 2 
        lngRowDestin = 4 
         
        Sheets("Sheet1").Range("F" & lngRowDestin & ":X" & lngRowDestin & "").Value = _ 
        Sheets("Sheet2").Range("B" & lngRowSource & ":T" & lngRowSource & "").Value 
         
    End Sub 
    
    
    HTH

    Robert

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    10th June 2010
    Location
    Seattle, WA
    Posts
    451

    Re: Set Cell Value Equal to Another using VBA

    The .cells property has some logic that makes it a little awkward for what you're trying to do. Basically, every time you reference a range using the cells([row],[column]) notation, VBA assumes you are refering to the active worksheet, unless otherwise specified immediately before the cells reference. When you hit this line of code, if either Sheet1 or Sheet2 is the active sheet, you could eliminate that qualifier from its side of the equation. Here's what it looks like with sheet specified in the necessary places:

    VB:
    Range(Sheet1.Cells(4, 6), Sheet1.Cells(4, 24)) = Range(Sheet2.Cells(2, 2), Sheet2.Cells(2, 20)).Value 
    
    
    It's kind of a mouthful. Might be easer to assemble to assemble an address string instead.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th March 2011
    Posts
    42

    Re: Set Cell Value Equal to Another using VBA

    Yeah, both works. Just personally I prefer .cells over range is you can pass variable directly instead to code the string address.
    Thank you two for saving my time again.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Setting a cell equal to another cell but as an equation
    By the_crooked_toe in forum EXCEL HELP
    Replies: 4
    Last Post: July 2nd, 2010, 03:47
  2. Replies: 1
    Last Post: June 26th, 2010, 10:46
  3. Replies: 2
    Last Post: September 25th, 2007, 06:55
  4. Delete Rows Where Cell Doesn't Equal Adjacent Cell
    By drmeatrx in forum EXCEL HELP
    Replies: 3
    Last Post: August 16th, 2007, 14:18
  5. Replies: 3
    Last Post: April 26th, 2006, 23:16

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
  •  
porno