Creating a loop
 

I am trying to create a loop that:

1. Starts on Sheet1 at cell A10 and loops down colum A until it finds
and empty cell. Could somebody please point me in the right direction,
I am starting to loose my mind.

Thank you in advance.
Simon.


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

Creating a loop
 

Option Explicit
Sub test()
Dim target As Range

Set target = GetFirstEmpty(Range("A10"))
target.Select
MsgBox target.Address

End Sub

Function GetFirstEmpty(StartFrom As Range) As Range

Dim target As Range

If StartFrom = "" Then
Set GetFirstEmpty = StartFrom
Else
Set GetFirstEmpty = StartFrom.End(xlDown).Offset(1)
End If

End Function


"shart" wrote:


I am trying to create a loop that:

1. Starts on Sheet1 at cell A10 and loops down colum A until it finds
and empty cell. Could somebody please point me in the right direction,
I am starting to loose my mind.

Thank you in advance.
Simon.


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

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.


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

Creating a loop
 

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.

Creating a loop
 

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.


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

Creating a loop
 

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.

Creating a loop
 

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



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


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

Creating a loop
 

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



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

Creating a loop
 

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

The debug stops the application at:

Code
-------------------

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

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


Within:

Code
-------------------
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 ar
writing. The same data needs to be populated into both documents i
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, (mysel
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 bot
documents (which is why we have a global sheet which we are trying t
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
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

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


+-------------------------------------------------------------------
|Filename: finalstep.gif
|Download: http://www.excelforum.com/attachment.php?postid=4136
+-------------------------------------------------------------------

--
shar
-----------------------------------------------------------------------
shart's Profile: http://www.excelforum.com/member.php...fo&userid=2956
View this thread: http://www.excelforum.com/showthread.php?threadid=49265

Creating a loop
 

Is anybody able to help me on this one? Please ..

--
shar
-----------------------------------------------------------------------
shart's Profile: http://www.excelforum.com/member.php...fo&userid=2956
View this thread: http://www.excelforum.com/showthread.php?threadid=49265

Creating a loop
 

Found a worable sollution.

1. Created an extra sheet within the first document, created all the
same fields that we need in the locked sheet the company sent us.


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

Sheets("Export").Range("A2:A" & eRow).Copy Destination:=Sheets("Motion").Range("A2:A" & eRow)

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


The other rows are pretty much the same.

Thank you for all your help.

(Btw. 'Excel 2003 VBA Programmer’s Reference' by Wrox was also a great
help.)


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