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