formula based on a string (2003)

[Jackal]

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

[JohnBF]

Shouldn't the syntax for that line be:

Range("F6").Formula = "=" & aVar & "FA6 - " & zVar & "FA6"

[LegareColeman]

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>

[Jackal]

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