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

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

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

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:


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

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.