+ Reply to Thread
Results 1 to 2 of 2

formula typed contains an error

  1. #1
    Forum Contributor
    Join Date
    06-04-2006
    Posts
    132

    formula typed contains an error

    Hi,

    I was wondering if anyone might be able to help me out. I've got some error message saying " the formula you typed contains an error" after putting in the following code.

    Here is my code,

    for i = 0 to ubound(x)
    c = chr(65 + i) + trim(str(Count) ' Count is the row number
    with worksheets("Sheet1")
    with .range(c$)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=my" & c$
    .FormatConditions(1).Interior.ColorIndex = 36
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="my" & c$
    .FormatConditions(1).Interior.ColorIndex = 42
    end with
    end with

    rowline1 = "my" & c$
    mysht = "=Sheet2!R" & cstr(Count) & "C" & cstr(i)
    with ActiveWorkbook
    .Names.Add Name:=rowline1, RefersToR1C1:=mysht
    end with
    ...

    next i

    Any ideas?

    Thank you in advance

  2. #2
    Chip Pearson
    Guest

    Re: formula typed contains an error

    The code will fail when i = 0 and the formula mysht is
    =Sheet2!R1C0

    Names.Add Name:=rowline1, RefersToR1C1:=mysht

    You need to configure you code so that it will work with a loop
    like

    For i = 1 To UBound(x)


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com




    "associates"
    <[email protected]> wrote
    in message
    news:[email protected]...
    >
    > Hi,
    >
    > I was wondering if anyone might be able to help me out. I've
    > got some
    > error message saying " the formula you typed contains an error"
    > after
    > putting in the following code.
    >
    > Here is my code,
    >
    > for i = 0 to ubound(x)
    > c = chr(65 + i) + trim(str(Count) ' Count is the row number
    > with worksheets("Sheet1")
    > with .range(c$)
    > FormatConditions.Delete
    > FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,
    > Formula1:="=my" & c$
    > FormatConditions(1).Interior.ColorIndex = 36
    > FormatConditions.Add Type:=xlCellValue,
    > Operator:=xlNotEqual, Formula1:="my" & c$
    > FormatConditions(1).Interior.ColorIndex = 42
    > end with
    > end with
    >
    > rowline1 = "my" & c$
    > mysht = "=Sheet2!R" & cstr(Count) & "C" & cstr(i)
    > with ActiveWorkbook
    > Names.Add Name:=rowline1, RefersToR1C1:=mysht
    > end with
    > ..
    >
    > next i
    >
    > Any ideas?
    >
    > Thank you in advance
    >
    >
    > --
    > associates
    > ------------------------------------------------------------------------
    > associates's Profile:
    > http://www.excelforum.com/member.php...o&userid=35073
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=550789
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1