Results 1 to 9 of 9

Thread: Syntax

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for asking, but I'm a little brain-dead tonight

    Code:
    For L4 = L3 To 2 Step -1
    		If Range("F" & L4) <> "RTS" Then
    			Range("F" & L4).EntireRow.Delete
    		Else
    			Range(("A" & L4), Range("R" & L4)).Interior.xlNone
    			Range(("A" & L4), Range("R" & L4)).Font.ColorIndex = 1
    		End If
    Code:
    With L1.PageSetup
    		.LeftMargin = Application.InchesToPoints(0.590551181102362)
    		.RightMargin = Application.InchesToPoints(0.590551181102362)
    		.Orientation = xlLandscape
    		.FitToPagesWide = 1
    		.FitToPagesTall = 1
    	End With
    1. What is wrong with my 'Else' syntax?
    2. Partial recording: The fit to 1x1 is not happening, so I am guessing that I have deleted a critical line of code??

    TIA.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    1a. ...Interior.xlNone makes no sense. Did you perhaps mean

    ...Interior.ColorIndex = xlColorIndexNone

    1b. The use of Range(("A" & L4), Range("R" & L4)), although not incorrect, is unusual. I'd use either

    Range(Range("A" & L4), Range("R" & L4))

    or

    Range("A" & L4 & ":R" & L4)

    2. You need a line

    .Zoom = False

    before the lines with .FitToPagesWide and .FitToPagesTall. See the built-in VBA help.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, may I ask one more

    If Left(Replace(Range("D" & L4), " ", "",6) <> "542011") Or Left(Replace(Range("D" & L4), " ", "",6) <> "554828") Then

    I'm being told that the "left" argument is not optional?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='VegasNath' post='781101' date='22-Jun-2009 15:07']If Left(Replace(Range("D" & L4), " ", "",6) <> "542011") Or Left(Replace(Range("D" & L4), " ", "",6) <> "554828") Then

    I'm being told that the "left" argument is not optional?[/quote]
    Without testing, looks like missplaced parens to close the Replace statement and encapsulate the two parts of the OR:

    (Left(Replace(Range("D" & L4), " ", ""),6) <> "542011") Or (Left(Replace(Range("D" & L4), " ", ""),6) <> "554828") Then

    but maybe I misunderstand the whole statement...parens are tricky that way. BTW, if the spaces you are removing on at the start or end of the cell value, VBA TRIM(string) works the same as Excel =TRIM(cell)
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Last one, I promise!

    Range(RngFind.Offset(0, -6)).Formula = "=TEXT(TODAY(),""dd/mm/yy"") & "" sent"""

    My debugging skills are not working tonight

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='781114' date='23-Jun-2009 01:13']Thanks John!

    Last one, I promise!

    Range(RngFind.Offset(0, -6)).Formula = "=TEXT(TODAY(),""dd/mm/yy"") & "" sent"""

    My debugging skills are not working tonight [/quote]
    It would help if you told us what you want to accomplish and in what way the code fails.

    Do you want to place the formula in RngFind.Offset(0, -6)? If so, use

    RngFind.Offset(0, -6).Formula = "=TEXT(TODAY(),""dd/mm/yy"") & "" sent"""

    Or do you want to place the formula in the cell whose address is stored in RngFind.Offset(0, -6)? If so, what goes wrong?

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

    RngFind.Offset(0, -6).Formula = "=TEXT(TODAY(),""dd/mm/yy"") & "" sent"""

    That is what I want, except:

    The "RngFind.Offset(0, -6)" cells are set as text, so how can i put the result of the formula there, or firstly change the cells to value?

    But, I want the result of the formula, not the formula itself?

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='VegasNath' post='781121' date='22-Jun-2009 16:28']I want the result of the formula, not the formula itself?[/quote]
    If you want the value:

    RngFind.Offset(0, -6).Value = CSTR(FORMAT(DATE, "dd/mm/yyyy")) & " sent"

    (untested, but should be close)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9

Posting Permissions

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