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.

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.

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.
>
>