Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formula based on a string (2003)

    hello eveyone,

    I have to following code that works fine:
    <pre>Private Sub AddSheet()
    Application.ScreenUpdating = False
    UnprotectWb
    Dim oWS As Worksheet
    Dim I As Long
    For I = 1 To Worksheets.Count
    On Error Resume Next
    Set oWS = Nothing
    Set oWS = Worksheets("ChgOrd" & I)
    On Error GoTo 0
    If oWS Is Nothing Then
    ActiveSheet.Copy before:=Sheets(1)
    Set oWS = ActiveSheet
    oWS.Name = "ChgOrd" & I
    Range("A1:FY1728").Select
    <font color=red>Dim sVariable As String
    If I > 1 Then
    sVariable = "ChgOrd" & I - 1
    Else
    sVariable = "PavBid"
    End If
    </font color=red>
    With Selection
    .FormatConditions.Delete
    If I > 1 Then
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(" & """" & sVariable & "!" & """" & " & CELL(""Address"",A1)))"
    Else
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(""PavBid!"" & Cell(""Address"",A1)))"
    End If
    End With
    'FormatCond
    Range("a1").Select
    Application.ScreenUpdating = True
    Exit Sub
    End If
    Next I
    ' ActiveSheet.Copy before:=Sheets(1)
    ' Set oWS = ActiveSheet
    ' oWS.Name = "ChgOrd" & I
    ' Application.ScreenUpdating = True
    End Sub
    </pre>

    I am trying to use I similar instance of the <pre>Dim sVariable As String</pre>

    in the following, why dies this not work?:

    <pre>Private Sub ChrOdrCost()
    Application.ScreenUpdating = False
    UnprotectWb
    Dim oWS As Worksheet
    Dim I As Long
    For I = 1 To Worksheets.Count
    On Error Resume Next
    Set oWS = Nothing
    Set oWS = Worksheets("ChgOrd" & I)
    On Error GoTo 0
    If oWS Is Nothing Then
    Beep
    'Range("F6:H224").Formula = "=PavBid!FA6"
    Exit Sub
    Else
    Dim aVar, bVar As String
    If I = 1 Then
    aVar = "PavBid"
    bVar = "ChgOrd" & I
    Worksheets("CostCodes (TEMP)").Visible = True
    Worksheets("CostCodes (TEMP)").Copy before:=Sheets(2)
    ActiveSheet.Name = "ChgOrd Cost"
    Worksheets("CostCodes (TEMP)").Visible = False
    <font color=blue>Range("F6").Formula = "= & aVar & FA6 & " - " & zVar & FA6"</font color=blue>
    Range("F6").Copy
    Range("F6:H224").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Else
    Set zVar = Worksheets("ChgOrd" & I)
    Set aVar = Worksheets("ChgOrd" & I - 1)

    End If
    Sheets("CostCodes").Select
    'Range("F6:H224").Formula

    End If
    Next I
    End Sub</pre>

    In the <font color=blue>blue</font color=blue> line i am getting an error, i don't know why?

    thanks,
    jackal

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formula based on a string (2003)

    Shouldn't the syntax for that line be:

    Range("F6").Formula = "=" & aVar & "FA6 - " & zVar & "FA6"
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula based on a string (2003)

    Without knowing WHAT error you are getting, and without the workbook so I can actually try the code to see what it is doing, I can only guess. My best guess is that you have some double quotes in the wrong place. Try this:

    <pre> Range("F6").Formula = "=" & aVar & FA6 & " - " & zVar & FA6
    </pre>


    I don't know what zVar is, so it might should be bVar.

    Or possibly this:

    <pre> Range("F6").Formula = "=" & aVar & "!FA6 - " & bVar & "!FA6"
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula based on a string (2003)

    Thanks to both of you for the help. The one I got to work was:

    <pre>Range("F6").Formula = "=" & aVar & "!FA6 - " & bVar & "!FA6"</pre>


    Thanks again,
    jackal

Posting Permissions

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