Line Created with Code Skips ahead when used

[mstuf]

Line Created with Code Skips ahead when used


The Result of this Code is left in Cell T -- I copy the Formula Bar Version and insert it into a Word Document.

Sub PrepOrders_From_T_2()
    
    Dim LASTROW As Long
    Dim MyCELL As Object
    Dim i As Long
    Dim rngT As Range
    
    Application.ScreenUpdating = False
    For Each MyCELL In Selection
 
        If (MyCELL.Column = 20) Then

            i = MyCELL.Row
            Set rngT = Range("T" & i)
            rngT.Value = Chr(10) & "Order Number:" & Space(1) & Range("A" & i) & Chr(10)
            rngT.Value = rngT.Value & "Purchase Date:" & Space(1) & Range("C" & i) & Chr(10)
            rngT.Value = rngT.Value & "Shipped By:" & Space(1) & Range("L" & i) & Chr(10) & Chr(13)
            rngT.Value = rngT.Value & "Shipping Address:" & Chr(10)
            rngT.Value = rngT.Value & Range("M" & i) & Chr(10)
            rngT.Value = rngT.Value & Range("N" & i) & Chr(10)
            
            ' check if anything in donor column
            If Range("O" & i) <> "" Then
                rngT.Value = rngT.Value & Range("O" & i) & Chr(10)
            End If
            
            
            rngT.Value = rngT.Value & Range("P" & i) & "," & Range("Q" & i) & "."
            rngT.Value = rngT.Value & Range("R" & i) & Chr(10)
            rngT.Value = rngT.Value & Range("S" & i) & Chr(10) & Chr(10)
            rngT.Value = rngT.Value & "Buyer Name:" & Space(1) & Range("F" & i) & Chr(10) & Chr(10) & Chr(10)
            rngT.Value = rngT.Value & "Item:" & Space(1) & Range("H" & i) & Chr(10)
            rngT.Value = rngT.Value & "SKU:" & Space(1) & Range("G" & i) & Chr(10)
            rngT.Value = rngT.Value & "Quantity:" & Space(1) & Range("I" & i) & Chr(10)
            'Problem in this area ?
            rngT.Value = rngT.Value & "---------------------------------" & Chr(10) & Chr(13)
            rngT.Value = rngT.Value & Chr(10) & "Order Number:" & Space(1) & Range("A" & i) & Chr(10)
            '
            rngT.Value = rngT.Value & "Purchase Date:" & Space(1) & Range("C" & i) & Chr(10)
            rngT.Value = rngT.Value & "Shipped By:" & Space(1) & Range("L" & i) & Chr(10) & Chr(13) & Chr(10)
            rngT.Value = rngT.Value & "Shipping Address:" & Chr(10)
            rngT.Value = rngT.Value & Range("M" & i) & Chr(10)
            rngT.Value = rngT.Value & Range("N" & i) & Chr(10)
                        
                        ' check if anything in donor column
            If Range("O" & i) <> "" Then
                rngT.Value = rngT.Value & Range("O" & i) & Chr(10)
            End If
            
            rngT.Value = rngT.Value & Range("P" & i) & "," & Range("Q" & i) & "."
            rngT.Value = rngT.Value & Range("R" & i) & Chr(10)
            rngT.Value = rngT.Value & Range("S" & i) & Chr(10)
            rngT.Value = rngT.Value & "Buyer Name:" & Space(1) & Range("F" & i) & Chr(10) & Chr(10)
            rngT.Value = rngT.Value & "Item:" & Space(1) & Range("H" & i) & Chr(10)
            rngT.Value = rngT.Value & "Quantity:" & Space(1) & Range("I" & i) & Chr(10)
            rngT.Value = rngT.Value & "SKU: " & Space(1) & Range("G" & i) & Chr(10)
            rngT.Value = rngT.Value & "---------------------------------" & Chr(10) & Chr(13)
            
        End If

    Next MyCELL
    ActiveCell.Font.Name = "Ariel"
    Selection.RowHeight = 90
    Selection.WrapText = True
    Application.ScreenUpdating = True
    
End Sub

Other Blank Lines in the Document can be edited from the Border of the Document just by clicking within the line and the Cursor appears at the Left Border.

When I attempt to edit (add text) to the First Line in the Middle of the Document - The One Just after the inserted ---------------'s , I click within the Line and the Cursor Appears one Space Right of the Border. When I attempt to add my input text, the cursor and text move down one line to appear. I can edit and move it back up.

If I actually place the Cursor at the Border Space of the Line -- My Text appears on the Line Typed as Desired.

I cant find anything in my Word Layout thats causing it since its pasted in as a Block.

<b>Is there something in my Excel VBA Code that is Causing this line to not work as other blank lines inserted both Above and Below it do ?? </b>

If I attempt edit from within the Formula Bar -- Clicking with the line of OTHER blanks, places the cursor at the Left Border. When doing the same thing in the aforementioned line it too placed the cursor one space in but when adding text it does not drop down a line but it does move one space left, out to the border.



Puzzled. I have tried other combinations of Line Break and Insert Blank Line code but have not hit on anything that makes a difference.

As Always -- thanks for Looking --

 

[mstuf]

Problem Solved -- I Just kept trying other combinations of CHR 10 and 13 & Vblf etc -- around the problem area and found some combination that worked. Not Sure why -- but its working as required.

Thanks to those who looked -

Moving on

 

[Norie]

Glad you found an answer.

I was actually going to respond, but I couldn't quite see where Word came into it.

Finally worked out you were copying from the formula bar.

But I've got to ask why?

Did you not try mail merge?

Apart from one part of the code it looks like you have the data in the proper format for it.

 

[mstuf]

Thanks Norie

Yes I did look at and play with Mail Merge --

If I were Starting Now to establish an order harvest routine, I would base it on Mail Merge. But, at this point -- with my understanding of what I have and ability ( ? ) to edit it and the fact that its working the best it ever has even after a recent format change from the Website, I just couldn't bring myself to attempt such a change.

I use my routine 6 days a week. I just started selling on another website that I will be eventually preparing another routine for. I will attempt to set it up with mail merge will its slow paced. Then, Once I am more familiar with MM, I may modify this routine to incorporate it.

I Appreciate the tip !

Thanks

 

[Norie]

Mail Merge is actually only one idea.

The thing I really don't understand is the copying from the formula bar and then pasting into Excel.

If that's how you transfer the data to Word you are bound to lose formatting, or even worse data.

Another thing I was going to suggest was creating a simple template in Word and, using automation, use code to transfer the data into it from Excel.

Anyway, just ideas - if you've got someting that works that's the important thing.

 

[mstuf]

Thanks

The thing I really don't understand is the copying from the formula bar and then pasting into Excel.

The Text that is Netted from the Formula Bar pastes differently than the Text that is Netted from a copy and paste of the Cell.


Yes -- its working well right now and I have just accomplished the next portion tonight and have it working for tomorrow usage. Acutally Looking forward to getting up in the morning and using it. What you are seeing in only a portion of my whole process which is why I'm afraid to try to switch horses in midstream now.

3 AM Here == off to Bed -- back in 8 ( usually 6 but I try for 8 ) .

Thanks again

 

[Norie]

No I realise why you are copying directly from the formula bar.

Perhaps what I should have said why are you copying and pasting from Excel to Word at all.

 

[mstuf]

This morning I just re read your comment

The thing I really don't understand is the copying from the formula bar and then pasting into Excel.

I speed read it , and thought it to say from Excel to Word and based my reply on that.

The Main Paste from Excel to Excel in my routine doesnt take place from the Formula Bar -- Its just a move of a whole row item from Open to the Sold Workbook when an item is Sold out. Constructing the data form for my word doc in Excel is done because of another project that I was successful at. I devised a way to construct HTML Documents with data from excel and photos from excel and then I adapted some of that code to this project. I just had to go with what I understood 4 years ago when I started to step up.


Much of what I do is based on foundation of what I learned or figured out how to make it do starting some years back. Once the foundation was built an added to, many times I just am weary to tear too much of it out and start over at this point.

Once I get some items on this new site I'm starting at -- I plan to try to start a new system and will try some new things. MM etc

Its an 80+ Mile Round trip to the Community College -- I have always wanted to take classes. We used to have a satelite CC Excel class out here but that evaporated just about the same time I started to try to learn this stuff. I read here and several other sites. I gain a lot from that but much here is rather advanced so I'm often left with the horse ahead of the Cart. There is no Structure to what you learn by reading random threads. I have bought Books but all seem to jump from Boiling water to Brain Surgery without enough details on what I do to leave me with best understanding. Most Books are heavy on User Form -- menu items and Message Box VBA. I gleen some from that. I realize that I am using Excel heavily as a Text Editor but .... I work an average of 14 Hours a day - on product preperation - Listing - Managing inventory - Preparing Paperwork and Shipping. Not a lot of time to go back to school at age 60. I have mountains more of stuff to list and only so much time to do it.

I have almost 40000 Lines in my open Workbook and almost 50000 in my sold Workbook and have been looking at Access as a Next Step. Been reading to try to get better understanding and game plan that I had when I started with Excel.



Thanks

 

[Norie]

Sorry, I actually meant from the Excel formula bar to Word.

There isn't any pasting on the code you posted anyway.

Anyway, I think I'll leave it for now - I'm confusing myself.