none
Application.Transpose RRS feed

  • Question

  • hi all reading my post,

    i am using a VBA code which fires each time a specific email comes in, in basic it opens an excel file & puts in some data, & also takes a table from a webPage and places it into a cell, then it needs to split the cell into rows for each NewLine a new cell, but my code worked in excel, & somehow in outlook it dont work, can someone tell me why?

    here is my code

    Dim r As Long, Sp
    
    For r = xlsheet2.Range("A" & xlsheet2.Rows.Count).End(xlUp).Row To 0 Step -1
      If InStr(xlsheet2.Range("A" & r), Chr(10)) > 0 Then
        Sp = Split(xlsheet2.Range("A" & r), Chr(10))
        Rows(r + 1).Resize(UBound(Sp)).Insert
        Rows(r).Copy Rows(r + 1).Resize(UBound(Sp))
        xlsheet2.Range("A" & r).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
      End If
    Next r

    few errors here, on application.transpose is an error, so i changed it to outlook.application.transpose and i still get an error "object dosent support this property or method"

    any help on that?

    Monday, December 24, 2012 2:53 AM

Answers

  • after working all night, i came to a solution, i saw other ppl having the same issue & they didnt got answered, so i hope my info will help them, & if so then give me a vote :)

    here is my code

    Dim Mysplit As Variant
    Dim i As Long
    Dim final As Range
    set final = xlsheet2.Range("A1")
    
    For i = 1 To final.Rows.Count
    Mysplit = Split(final.Resize(1).Offset(i - 1), vbLf)
    final.Offset(i - 1).Resize(1, UBound(Mysplit) + 1).Offset(, 1) = Mysplit
    Next

    again if this helps you, then give a Vote,

    thanks.

    • Marked as answer by Sol Stein Monday, December 24, 2012 3:43 PM
    Monday, December 24, 2012 3:43 PM

All replies

  • there is no Transpose on outlook Application object, only on excel one. if you want that you can get that instance for example using xlsheet2.Application.Transpose
    Monday, December 24, 2012 12:01 PM
  • after working all night, i came to a solution, i saw other ppl having the same issue & they didnt got answered, so i hope my info will help them, & if so then give me a vote :)

    here is my code

    Dim Mysplit As Variant
    Dim i As Long
    Dim final As Range
    set final = xlsheet2.Range("A1")
    
    For i = 1 To final.Rows.Count
    Mysplit = Split(final.Resize(1).Offset(i - 1), vbLf)
    final.Offset(i - 1).Resize(1, UBound(Mysplit) + 1).Offset(, 1) = Mysplit
    Next

    again if this helps you, then give a Vote,

    thanks.

    • Marked as answer by Sol Stein Monday, December 24, 2012 3:43 PM
    Monday, December 24, 2012 3:43 PM