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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

The whole script so far

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Within:

Please Login or Register  to view this content.

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

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

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.

Please Login or Register  to view this content.

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