Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a loop
Hi, Can you please help
My spreadsheet looks like this Column A, Column B, Column C Blank 600 8172540 Blank 18/12/2008 Blank 19/12/2008 Blank 19/12/2008 Blank 600 8172559 Blank 18/12/2008 Blank 21/12/2008 Result required Column A, Column B, Column C Blank 600 8172540 8172540 18/12/2008 8172540 19/12/2008 8172540 19/12/2008 Blank 600 8172559 8172559 18/12/2008 8172559 21/12/2008 I am trying to create a loop in a macro that will look at column B, identify the value of 600 and copy the value in column c into column A until the value of 600 in column B re-occurs at which point it then copies Column C into Column A until 600 occurs in Column B etc etc, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a loop
Sub Tryme()
mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row holdme = Cells(1, 3) For j = 2 To mylast If Cells(j, 3) = "" Then Cells(j, 1) = holdme Else holdme = Cells(j, 3) End If Next End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "stephenc" wrote in message ... Hi, Can you please help My spreadsheet looks like this Column A, Column B, Column C Blank 600 8172540 Blank 18/12/2008 Blank 19/12/2008 Blank 19/12/2008 Blank 600 8172559 Blank 18/12/2008 Blank 21/12/2008 Result required Column A, Column B, Column C Blank 600 8172540 8172540 18/12/2008 8172540 19/12/2008 8172540 19/12/2008 Blank 600 8172559 8172559 18/12/2008 8172559 21/12/2008 I am trying to create a loop in a macro that will look at column B, identify the value of 600 and copy the value in column c into column A until the value of 600 in column B re-occurs at which point it then copies Column C into Column A until 600 occurs in Column B etc etc, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a loop
Hi Bernard
Thank you for your quick response however I tried this and nothing happened? "Bernard Liengme" wrote: Sub Tryme() mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row holdme = Cells(1, 3) For j = 2 To mylast If Cells(j, 3) = "" Then Cells(j, 1) = holdme Else holdme = Cells(j, 3) End If Next End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "stephenc" wrote in message ... Hi, Can you please help My spreadsheet looks like this Column A, Column B, Column C Blank 600 8172540 Blank 18/12/2008 Blank 19/12/2008 Blank 19/12/2008 Blank 600 8172559 Blank 18/12/2008 Blank 21/12/2008 Result required Column A, Column B, Column C Blank 600 8172540 8172540 18/12/2008 8172540 19/12/2008 8172540 19/12/2008 Blank 600 8172559 8172559 18/12/2008 8172559 21/12/2008 I am trying to create a loop in a macro that will look at column B, identify the value of 600 and copy the value in column c into column A until the value of 600 in column B re-occurs at which point it then copies Column C into Column A until 600 occurs in Column B etc etc, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a loop
I ran it and it worked.
You need to open the VBA editor: Tools/Macros/VBEditor In the VBE window, in the Project window ( far left) select (click on) the workbook you are using On the VBE toolbar: Insert | Module Now paste the code I gave you Return to worksheet; use Tools | Macros| Marcos and click on "Tryme" For more info, visit David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "stephenc" wrote in message ... Hi Bernard Thank you for your quick response however I tried this and nothing happened? "Bernard Liengme" wrote: Sub Tryme() mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row holdme = Cells(1, 3) For j = 2 To mylast If Cells(j, 3) = "" Then Cells(j, 1) = holdme Else holdme = Cells(j, 3) End If Next End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "stephenc" wrote in message ... Hi, Can you please help My spreadsheet looks like this Column A, Column B, Column C Blank 600 8172540 Blank 18/12/2008 Blank 19/12/2008 Blank 19/12/2008 Blank 600 8172559 Blank 18/12/2008 Blank 21/12/2008 Result required Column A, Column B, Column C Blank 600 8172540 8172540 18/12/2008 8172540 19/12/2008 8172540 19/12/2008 Blank 600 8172559 8172559 18/12/2008 8172559 21/12/2008 I am trying to create a loop in a macro that will look at column B, identify the value of 600 and copy the value in column c into column A until the value of 600 in column B re-occurs at which point it then copies Column C into Column A until 600 occurs in Column B etc etc, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a loop
Hi Bernard
I have this working now Thank you "Bernard Liengme" wrote: I ran it and it worked. You need to open the VBA editor: Tools/Macros/VBEditor In the VBE window, in the Project window ( far left) select (click on) the workbook you are using On the VBE toolbar: Insert | Module Now paste the code I gave you Return to worksheet; use Tools | Macros| Marcos and click on "Tryme" For more info, visit David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "stephenc" wrote in message ... Hi Bernard Thank you for your quick response however I tried this and nothing happened? "Bernard Liengme" wrote: Sub Tryme() mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row holdme = Cells(1, 3) For j = 2 To mylast If Cells(j, 3) = "" Then Cells(j, 1) = holdme Else holdme = Cells(j, 3) End If Next End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "stephenc" wrote in message ... Hi, Can you please help My spreadsheet looks like this Column A, Column B, Column C Blank 600 8172540 Blank 18/12/2008 Blank 19/12/2008 Blank 19/12/2008 Blank 600 8172559 Blank 18/12/2008 Blank 21/12/2008 Result required Column A, Column B, Column C Blank 600 8172540 8172540 18/12/2008 8172540 19/12/2008 8172540 19/12/2008 Blank 600 8172559 8172559 18/12/2008 8172559 21/12/2008 I am trying to create a loop in a macro that will look at column B, identify the value of 600 and copy the value in column c into column A until the value of 600 in column B re-occurs at which point it then copies Column C into Column A until 600 occurs in Column B etc etc, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a for loop | Excel Discussion (Misc queries) | |||
Loop | Excel Discussion (Misc queries) | |||
Use a loop to create multiple Charts - Suggestions ? | Charts and Charting in Excel | |||
help with a loop | Excel Discussion (Misc queries) | |||
Which loop to use | Excel Discussion (Misc queries) |