Thank you for all the help so far. I have one more step and them I am
finished.

1. I need to add a formula into a seporate XLS document based pretty
much on the same set of rules in the first document.

a. in cell D10 (and every line below)
=IF(LEN([Export.xls]Global!B10)0,Global!B10,"")
However, I only want to add this to lines which already have data
in Export.xls


The snippet I am having problems with:

Code:
--------------------

Workbooks.Open Filename:= _
"\\server\CCS\PriceApps\Motion.xls"

Windows("Motion.xls").Activate
With Sheets("UploadData")

.Range("D10:D" & eRow).FormulaR1C1 = _
"=IF(LEN([Export.xls]Global!RC[1])0,[Export.xls]Global!RC[1],"""")"

End With

--------------------



The whole script so far:

Code:
--------------------

Private Sub CommandButton1_Click()

Dim eRow As Long
eRow = Sheets("Global").Range("A10").End(xlDown).Row

With Sheets(" Export")

.Range("A10:B" & eRow).FormulaR1C1 = _
"=Global!RC"

.Range("C10:C" & eRow).FormulaR1C1 = _
"=Global!RC[1]&"" ""&Global!RC[3]&"" ""&Global!RC[4]"

.Range("C10:C" & eRow).FormulaR1C1 = _
"=Global!RC[1]&"" ""&Global!RC[3]&"" ""&Global!RC[4]"
' =Global!D2&" "&Global!F2&" "&Global!G2

' Copy sale price and check the sale price against normal price
.Range("D10:D" & eRow).FormulaR1C1 = _
"=IF(LEN(Global!RC[1])0,Global!RC[1],"""")"

Dim i As Long
ThisWorkbook.Colors(6) = RGB(234, 136, 136)

With Sheets(" Export")
.Range("D10:D" & eRow).FormulaR1C1 = _
"=IF(LEN(Global!RC[1])0,Global!RC[1],"""")"
For i = 1 To eRow
If Sheets("Global").Range("E" & i) _
Sheets("Global").Range("D" & i) Then
.Range("D" & i).Interior.ColorIndex = 6
End If
Next i
End With

.Range("E10:E" & eRow).FormulaR1C1 = _
"=IF(LEN(Global!RC[3])0,Global!RC[3],"""")"

.Range("F10:F" & eRow).FormulaR1C1 = _
"=IF(LEN(Global!RC[3])0,Global!RC[3],"""")"

End With


Workbooks.Open Filename:= _
"\\server\shukr amman\CCS\PriceApps\Motion.xls"

Windows("Motion.xls").Activate
With Sheets("UploadData")

.Range("D10:D" & eRow).FormulaR1C1 = _
"=IF(LEN([Export.xls]Global!RC[1])0,[Export.xls]Global!RC[1],"""")"

End With



'----------------------------------
' Last Step
'----------------------------------
Windows("Export.xls").Activate
Set Target = Sheets("Export")
Target.Select

End Sub



--------------------


--
shart
------------------------------------------------------------------------
shart's Profile: http://www.excelforum.com/member.php...o&userid=29563
View this thread: http://www.excelforum.com/showthread...hreadid=494532