Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: one liner required

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to put the cells(2,4), Cells(3,5), cells(8,3) into Range("A1:A3") in a different workbook.

    A one liner in VBA code would do the trick.

    Any suggestions, thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I meant the VALUE of cells(.....) into
    Range("A1:A3")

    Cheers

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ret
    assuming that the sehhet with the cells is the selected one

    Sheets(2).Range("a1:a3") = WorksheetFunction.Transpose(Array(Cells(2, 4).Value, Cells(3, 5).Value, Cells(8, 6)))

    regards Tommy

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sehhet = sheet

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-23 10:19, RET79 wrote:
    I need to put the cells(2,4), Cells(3,5), cells(8,3) into Range("A1:A3") in a different workbook.

    A one liner in VBA code would do the trick.

    Any suggestions, thanks.
    Hi,

    Since you are going to have to reference two workbooks, a single line of code will be a mess to look at and debug.

    ---begin VBA---
    Sub test()

    Set orig = ThisWorkbook.Sheets("Sheet1")
    Set dest = Workbooks("TestBook.xls").Sheets("Sheet1")

    listarray = Array(orig.Cells(2, 4), orig.Cells(3, 5), orig.Cells(8, 3))
    dest.Range("A1:A3") = WorksheetFunction.Transpose(listarray)

    End Sub
    ---end VBA---

    Bye,
    Jay

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you really need a one-liner, then if (and only if) the source sheet is the active sheet, the shortest code is probably :-

    Workbooks("TestBook.xls").Sheets("Sheet1").[A1:A3].Value = WorksheetFunction.Transpose(Array([D2], [E3], [C8]))

    If the source sheet is not the active sheet, then as Jay Petrulius mentions, a one-liner would be a bit unweildy.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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