No announcement yet.

Set Cell Value Equal to Another using VBA

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    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?

  • #2
    Re: Set Cell Value Equal to Another using VBA

    Hi Guner,

    You can use variables in ranges, i.e.

    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



    • #3
      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:

          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.


      • #4
        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.