Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable problem (2000)

    Two part tuesday morning problem...


    <font color=blue>sTest = ThisWorkbook.Worksheets("Line 2").Range("S14").Value</font color=blue>

    is my code, i've been playing with it for ages, and can't find how to introduce a variable in to the equation, eg:

    <font color=blue>MyLoop = 14
    sTest = ThisWorkbook.Worksheets("Line 2").Range("S & MyLoop & ").Value</font color=blue>

    I've tried all manner of speech mark combinations but still can't get it.



    Secondly, is there a way to make this line of code, copy the value, and not the formula in the cell.

    <font color=blue>Sheets("Line 2").Range("A14:Q14").Select

    Selection.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Worksheets("Email").Range("A10")</font color=blue>

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

    Re: Variable problem (2000)

    1. The "S" is fixed, so that must be in quotes; the MyLoop part is variable, so that must be outside the quotes. Use

    sTest = ThisWorkbook.Worksheets("Line 2").Range("S" & MyLoop).Value

    2. You must use PasteSpecial for this; this means you must use 2 statements:

    Worksheets("Line 2").Range("A14:Q14").SpecialCells(xlCellTypeVisibl e).Copy
    Worksheets("Email").Range("A10").PasteSpecial Paste:=xlPasteValues

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable problem (2000)

    Thanks Hans, as usual you have the answer, and i'm another small step in the path to enlightenment !

    You know too much ! <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/artist.gif border=0 alt=artist width=34 height=29> <img src=/S/clown.gif border=0 alt=clown width=15 height=15> <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

Posting Permissions

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