VBA Formatting Help Needed Urgently!!!!!!!
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VBA Formatting Help Needed Urgently!!!!!!!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Ok I Have Made This Code That Sends Data From My Invoice System For My Freight forwarding Company To A Sort Of Database Here Is The Code:

    Sub Send_To_Database()
    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G11].Copy
    Sheets("Database").Select
    [A65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[B11].Copy
    Sheets("Database").Select
    [B65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[E13].Copy
    Sheets("Database").Select
    [C65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[B13].Copy
    Sheets("Database").Select
    [D65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[B15].Copy
    Sheets("Database").Select
    [E65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[B16].Copy
    Sheets("Database").Select
    [F65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G18].Copy
    Sheets("Database").Select
    [G65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G19].Copy
    Sheets("Database").Select
    [H65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G20].Copy
    Sheets("Database").Select
    [I65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G21].Copy
    Sheets("Database").Select
    [J65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G22].Copy
    Sheets("Database").Select
    [K65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Sheets("Automated Invoice").[G49].Copy
    Sheets("Database").Select
    [L65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    Sheets("Automated Invoice").Select
    Application.ScreenUpdating = True
    End Sub


    Ok The Code Works Perfectly However Because The Data Is On My Invoice Tempalte Is Formatted It Transfers Thr Formattted Text On To The Database I Don't Want This To Happen I Would Like It Formatted As 'Arial 10' Does Anyone Know How To Do This If So Help Is Needed Thanks,


    (PS that template thing doesn't work for me with the MS add ons as there is various complcations on my invoice system that prevents that so I need to do it this way so please don't suggesst top do that I know this is the way I have to do it!)

    Your Pal Mozz

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure but try adding
    With ActiveCell.Characters(Start:=1, Length:=14).Font
    .Name = "Arial"
    .Size = 10

    End With
    into your code

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Moz

    I hope you don't mind me saying, but you are going about this the long way. Try some code like this:

    Code:
    Sub Send_To_Database()
    Dim i As Integer
    Dim strRange As Range
    
    For i = 1 To 12
        strRange = Choose(i, "G11", "B11", "E13", "B13", "B15", "B16", _
                             "G18", "G19", "G20", "G21", "G22", "G49")
                             
        Sheets("Automated Invoice").Range(strRange).Copy Destination:= _
        Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0)
        With Sheets("Database").Range("A65536").End(xlUp).Font
            .Name = "Arial"
            .Size = 10
        End With
        
    Next i
    
    End Sub
    Much easier to read and De-bug.



  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank You Very Much For Your Help

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi David One Little Problem With Your Code Don't Worry I Thought I Was Going The Long Way Anyway But I Have Replaced Your Code With Mine And When I try To Execute The Code A Debug Error Message Comes Up Saying


    "Object Variable With Block Variable Not Set"

    and when I go to debug this is what is highlighted


    strRange = Choose(i, "G11", "B11", "E13", "B13", "B15", "B16", _
    "G18", "G19", "G20", "G21", "G22", "G49")

    just a little more help please! we are nearly there!!!

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    someone please

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    come on someone

  8. #8
    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-04-08 10:46, Morrissey wrote:
    come on someone
    Hi,

    I don't see the problem off hand. Make sure you have the "end with" in the statement at the end.

    Please copy and paste your code exactly as you have it. We need to compare if there are any typos or anything missing from Dave's code and what you have.

    Bye,
    Jay

  9. #9
    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

      
    Hi,

    There is a type mismatch in the code because strRange is dimensioned as a Range, but then it is used as a String.

    Change

    Dim strRange As Range
    to
    Dim strRange As String

    and it should work for you.

    Bye,
    Jay

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
  •  

 

 
DMCA.com