#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a for loop Jeff Excel Discussion (Misc queries) 1 November 8th 06 10:27 PM
Loop Heather O'Malley Excel Discussion (Misc queries) 1 November 6th 06 03:39 PM
Use a loop to create multiple Charts - Suggestions ? APOEL Charts and Charting in Excel 1 July 29th 06 03:36 AM
help with a loop BeJay Excel Discussion (Misc queries) 3 May 19th 06 12:24 PM
Which loop to use theguz Excel Discussion (Misc queries) 2 August 5th 05 07:01 PM


All times are GMT +1. The time now is 10:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"