Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Assistance (2002)

    Hi,
    If J55 is the active cell in the active worksheet, I am trying to construct a line of code using some sort of active cell offset, kind of as follows:

    Mem1.Value = "Memo XXX - XXX XXXXX " & Range("G55",00,000.00).Value & " REF " & Range("C55").Value & " DATE " & Range(B55,dd/mm/yy).value

    I'm unsure as to how...
    Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    If J55 is the active cell, you can refer to G55 as ActiveCell.Offset(0, -3) i.e. 0 rows down, 3 columns to the left.
    And you can refer to C55 as ActiveCell.Offset(0, -7), i.e. 0 rows down, 7 columns to the left.
    Similar for B55.

    Range("G55",00,000.00) makes no sense, try Format(ActiveCell.Offset(0, -3), "#,##0.00")
    Neither does Range(B55,dd/mm/yy) make sense, try Format(ActiveCell.Offset(0, -8), "dd/mm/yy")

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    Thanks, So:

    Mem1.Value = "Memo XXX - XXX XXXXX " & Format(ActiveCell.Offset(0, -3), "#,##0.00".Value & " REF " & ActiveCell.Offset(0, -7).Value & " DATE " & Format(ActiveCell.Offset(0, -8), "dd/mm/yy").value

    Or lose the .value? (I can't test it til Monday)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    Format(ActiveCell.Offset(0, -3), "#,##0.00".Value

    is not correct, it should be

    Format(ActiveCell.Offset(0, -3), "#,##0.00")

    You should omit the .Value, since that is a property of the cell, not of the Format function.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    So I assume the same for the third, but not for the second:

    Mem1.Value = "Memo XXX - XXX XXXXX " & Format(ActiveCell.Offset(0, -3), "#,##0.00") & " REF " & ActiveCell.Offset(0, -7).Value & " DATE " & Format(ActiveCell.Offset(0, -8), "dd/mm/yy")

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    Although it isn't necessary, you could also change ActiveCell.Offset(0, -7).Value to ActiveCell.Offset(0, -7) since Value is the default property of a range, so it's assumed if you omit it.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    Thanks.
    If I want to use quotation marks in a text string, am I right in thinking:

    Mem3.Value = "RCVD WITH INVALID REF """" & ActiveCell.Offset(0, -6) & """

    should work?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    No, it should be
    <code>
    Mem3.Value = "RCVD WITH INVALID REF """ & ActiveCell.Offset(0, -6) & """"
    </code>
    You have to replace <code>"</code> with <code>""</code> within a quoted string.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    Understood, Thanks.
    New subject: I am trying to send the data from an excel form by email in outlook.
    I am getting a compile error: User-defined type not defined on line: objOL As New Outlook.Application. Code attached.
    What am I doing wrong?
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    You have to set a reference to the Outlook object library. To do so:

    - Activate the Visual Basic Editor.
    - Select Tools | References...
    - Scroll down the list until you see Microsoft Outlook 10.0 Object Library (10.0 is the internal version number of Office XP/2002).
    - Tick its check box.
    - Click OK.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    It is not necessary to select cells before doing something with them. You can replace the lines

    Range("E5,E7,E9,E13,H13,E15,H15,E19,G19,I19,E21,G2 1,I21,E23,G23,I23,E25,G25,I25, _
    E27,G27,I27,E29,G29,I29,E31,G31,I31,E33,G33,I33,E3 5,G35,I35,E37,G37,I37,E39").Select
    Range("E39").Activate
    Selection.ClearContents
    Range("E5").Select

    with this:

    Range("E5,E7,E9,E13,H13,E15,H15,E19,G19,I19,E21,G2 1,I21,E23,G23,I23,E25,G25,I25, _
    E27,G27,I27,E29,G29,I29,E31,G31,I31,E33,G33,I33,E3 5,G35,I35,E37,G37,I37,E39").ClearContents

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    Edited by HansV to break very long line

    That worked Thanks.

    I have a locked worksheet set up as a form. To clear all the unlocked cells, I have the following code which does what I need, but there must be an easier way?

    Sub ClearContents()

    Range("E5,E7,E9,E13,H13,E15,H15,E19,G19,I19,E21,G2 1,I21,E23,G23,I23,E25,G25,I25,E27,G27,I27, _
    E29,G29,I29,E31,G31,I31,E33,G33,I33,E35,G35,I35,E3 7,G37,I37,E39").Select
    Range("E39").Activate
    Selection.ClearContents
    Range("E5").Select
    End Sub

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    & Format("E19", "dd/mm/yy") & " - " & Format("G19", "#,##0.00") & " - " & Range("I19")

    Why is this outputing (from excel to Outlook) as the cell ref instead of the result? I19 shows ok.
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (2002)

    Because you supply the cell address as a literal string, instead of a Range object. You should use Format(Range("E19"), "dd/mm/yy") etc.

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (2002)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Thankyou!

    How do I add sheet object code to a button. The code does not show up in the usual right click.

Page 1 of 2 12 LastLast

Posting Permissions

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