Creating a loop

Thank You,

My next question:

Now that the code is looping through the 1st column, I would like to start copying data from that row to other sheets. I would also like to input formuls. ( I have a formula already)

eg.

Sheet1(A10) copied to Sheet2(A10)
Sheet1(B10) copied to Sheet2(B10)
Put formula (=Sheet1!D10&" "&Sheet1!F10&" "&Sheet1!G10) into Sheet2(C10)
(I would like to copy this from a previous cell in the same column so that the 10's
change to 11's etc.)
Put Formula (=Sheet1!B10) into Sheet2(B10)


If I have these, I am pretty sure I can work the rest out.

Thank you in advance
Simon Hart.

If I understand correctly then I am not sure you need a loop at all. You
could just find the last used row in your range on sheet1 (I have done
this by starting at Cell A10 and moving down to first blank row) and
then use this variable to set the formulae on sheet2 eg:

Sub cpy()
Dim eRow As Long
eRow = Sheets("Sheet1").Range("A10").End(xlDown).Row
With Sheets("Sheet2")
.Range("A10:B" & eRow).FormulaR1C1 = _
"=Sheet1!RC"
.Range("C10:C" & eRow).FormulaR1C1 = _
"=Sheet1!RC[1]&"" ""&Sheet1!RC[3]&"" ""&Sheet1!RC[4]"
End With
End Sub

Hope this helps
Rowan

shart wrote:
> Thank You,
>
> My next question:
>
> Now that the code is looping through the 1st column, I would like to
> start copying data from that row to other sheets. I would also like to
> input formuls. ( I have a formula already)
>
> eg.
>
> Sheet1(A10) copied to Sheet2(A10)
> Sheet1(B10) copied to Sheet2(B10)
> Put formula (=Sheet1!D10&" "&Sheet1!F10&" "&Sheet1!G10) into
> Sheet2(C10)
> (I would like to copy this from a previous cell in the same column
> so that the 10's
> change to 11's etc.)
> Put Formula (=Sheet1!B10) into Sheet2(B10)
>
>
> If I have these, I am pretty sure I can work the rest out.
>
> Thank you in advance
> Simon Hart.
>
>

Thank You. The code works perfectly. From your code I was able to create the code for the rest of the sheet.

I have another question.

One of the lines looks like this:

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

I would like to add some VBA that changes the formatting of the cell if Global!RC[1] is greater than Global!RC[0]
(i.e. if the sale price is greater than the normal price, change the background color on Sheet2(D10) to red. This would make it easy to spot errors in Global)

Thank you in advance.
Simon.

Ps. Thank you for all the help so far, without it I would still be stuck copying and pasting from various VBA books and tutorials trying to figure out what is what.

Other goals for this little application (getting there):
1. Exporting Sheet2 as a CSV file.

That may need a loop. Somthing like:

Dim i As Long
With Sheets("Sheet2")
.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 = 3
End If
Next i
End With

For the export to CSV see JE McGimpsey's notes at:

http://www.mcgimpsey.com/excel/textfiles.html

Hope this helps
Rowan

shart wrote:
> Thank You. The code works perfectly. From your code I was able to
> create the code for the rest of the sheet.
>
> I have another question.
>
> One of the lines looks like this:
>
>
> Code:
> --------------------
> .Range("D10:D" & eRow).FormulaR1C1 = _
> "=IF(LEN(Global!RC[1])>0,Global!RC[1],"""")"
> --------------------
>
>
> I would like to add some VBA that changes the formatting of the cell if
> Global!RC[1] is greater than Global!RC[0]
> (i.e. if the sale price is greater than the normal price, change the
> background color on Sheet2(D10) to red. This would make it easy to spot
> errors in Global)
>
> Thank you in advance.
> Simon.
>
> Ps. Thank you for all the help so far, without it I would still be
> stuck copying and pasting from various VBA books and tutorials trying
> to figure out what is what.
>
> Other goals for this little application (getting there):
> 1. Exporting Sheet2 as a CSV file.
>
>

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

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

The whole script so far

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

That seems to run for me. What is the problem you are having eg
unexpected results, error messages etc?

Regards
Rowan

shart wrote:
> 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\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
>
> --------------------
>
>
>
> 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
>
>
>
> --------------------
>
>

Application Error '1004'
Application-defined or object-defined error.#

The debug stops the application at:

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

Within:

Workbooks.Open Filename:= _
        "Z:\CCS\PriceApps\OrderMotion.xls"
    
Windows("OrderMotion.xls").Activate
With Sheets("UploadData")

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

End With

Basically, we have two excel sheets to update the prices of our items, one locked which we cannot add macros to, and the other one we are writing. The same data needs to be populated into both documents in different formats.

The relevant documents are setup as follows:

Document 1 (Master Document - Export.xls)
-------------------------------------------------------
Sheet 1 - Global Data (starting on line 10)
Sheet 2 - CSV Export for Upload feature within backend of application.

Document 2 (Locked, cannot add macros to it - Motion.xls)
--------------------------------------------------------------------------
Sheet 1 - UploadData (All the data comes from ([Export.xls]global)

I would like to make this as idiot proof as possible, (myself excluded). Currently I have three buttons:

1 - Copy Data (The script I am having problems with)
2 - Export Data (Done, working)
3 - Upload Data (Document 2 - Working)

As you can see in the attachments, the columns are different for both documents (which is why we have a global sheet which we are trying to copy into the to documents without having to type it all in manually.)

Thank You.
Simon.

That seems to run for me. What is the problem you are having eg
unexpected results, error messages etc?

Regards
Rowan

shart wrote:
> 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\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
>
> --------------------
>