Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
Dear Undisguised Angels,
I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
Hi Matilda
In which cell do you want to copy every value you find in the column I will make a example for you if you give me this information -- Regards Ron de Bruin http://www.rondebruin.nl "Matilda" wrote in message ... Dear Undisguised Angels, I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
Thanks, Ron!
A picture of my data: Column A SearchColumn DestColumn Mary E Mary Joan LD Peter N Alex E Alex Eddie D I want to loop down SearchColumn until I find a certain value (eg "E"), then take the value from the offset -1 cell and write it to DestColumn. I then want to resume search at last valid cell in SearchColumn and repeat the process. "Ron de Bruin" wrote: Hi Matilda In which cell do you want to copy every value you find in the column I will make a example for you if you give me this information -- Regards Ron de Bruin http://www.rondebruin.nl "Matilda" wrote in message ... Dear Undisguised Angels, I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
This code looks for the value 7 in kolom A, when the value has been found he
copy it to kolom E. "i" is de row to start and "m" is de row to start copy the values to Sub test() Dim i As Long, m As Long m = 2 For i = 2 To Range("A1").CurrentRegion.Rows.Count If Cells(i, 1).Value = 7 Then Cells(m, 5).Value = Cells(i, 1) m = m + 1 End If Next End Sub -- Ronald Ferdinandus http://www.ro-pay.nl "Matilda" wrote: Dear Undisguised Angels, I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
SearchColumn = column B (2)
DestColumn= column E (5) It also looks voor "e" and "E", if yoy onlu want to look for "E", you can leave UCase out of it. Sub test() Dim i As Long For i = 2 To Range("B1").CurrentRegion.Rows.Count If UCase(Cells(i, 2).Value) = "E" Then Cells(i, 5).Value = Cells(i, 1) End If Next End Sub -- Ronald Ferdinandus http://www.ro-pay.nl "Matilda" wrote: Thanks, Ron! A picture of my data: Column A SearchColumn DestColumn Mary E Mary Joan LD Peter N Alex E Alex Eddie D I want to loop down SearchColumn until I find a certain value (eg "E"), then take the value from the offset -1 cell and write it to DestColumn. I then want to resume search at last valid cell in SearchColumn and repeat the process. "Ron de Bruin" wrote: Hi Matilda In which cell do you want to copy every value you find in the column I will make a example for you if you give me this information -- Regards Ron de Bruin http://www.rondebruin.nl "Matilda" wrote in message ... Dear Undisguised Angels, I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
Dear RonaldF,
Your code is wonderful. I can adapt it to my problem and it works! Thankyou for thinking of the case, that would have been the next problem... Much obliged Matilda "RonaldF" wrote: SearchColumn = column B (2) DestColumn= column E (5) It also looks voor "e" and "E", if yoy onlu want to look for "E", you can leave UCase out of it. Sub test() Dim i As Long For i = 2 To Range("B1").CurrentRegion.Rows.Count If UCase(Cells(i, 2).Value) = "E" Then Cells(i, 5).Value = Cells(i, 1) End If Next End Sub -- Ronald Ferdinandus http://www.ro-pay.nl "Matilda" wrote: Thanks, Ron! A picture of my data: Column A SearchColumn DestColumn Mary E Mary Joan LD Peter N Alex E Alex Eddie D I want to loop down SearchColumn until I find a certain value (eg "E"), then take the value from the offset -1 cell and write it to DestColumn. I then want to resume search at last valid cell in SearchColumn and repeat the process. "Ron de Bruin" wrote: Hi Matilda In which cell do you want to copy every value you find in the column I will make a example for you if you give me this information -- Regards Ron de Bruin http://www.rondebruin.nl "Matilda" wrote in message ... Dear Undisguised Angels, I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning to last cell in a do until loop
Sorry for the late response, I lost my internet connection..
Ok Try this one Sub Tester() Dim FirstAddress As String Dim myArr As Variant Dim rng As Range Dim I As Long Application.ScreenUpdating = False myArr = Array("E") 'You can also use more values in the Array 'myArr = Array("ron", "dave") With Range("B:B") .Offset(0, 1).ClearContents 'clear the cells in the column to the right, Col C in this example For I = LBound(myArr) To UBound(myArr) Set rng = .Find(What:=myArr(I), _ After:=Range("B" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) 'If you want to find a part of the rng.value then use xlPart 'if you use LookIn:=xlValues it will also work with a 'formula cell that evaluates to "E" If Not rng Is Nothing Then FirstAddress = rng.Address Do rng.Offset(0, 1).Value = rng.Offset(0, -1).Value Set rng = .FindNext(rng) Loop While Not rng Is Nothing And rng.Address < FirstAddress End If Next I End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Matilda" wrote in message ... Thanks, Ron! A picture of my data: Column A SearchColumn DestColumn Mary E Mary Joan LD Peter N Alex E Alex Eddie D I want to loop down SearchColumn until I find a certain value (eg "E"), then take the value from the offset -1 cell and write it to DestColumn. I then want to resume search at last valid cell in SearchColumn and repeat the process. "Ron de Bruin" wrote: Hi Matilda In which cell do you want to copy every value you find in the column I will make a example for you if you give me this information -- Regards Ron de Bruin http://www.rondebruin.nl "Matilda" wrote in message ... Dear Undisguised Angels, I am wanting to loop through a column looking for a value, then write that value to another cell, then return to the column to continue the search to the end. I can get to the target cell, but how can I return to the last "true" cell before continuing the loop? I'm trying a for....next loop but I'm afraid the syntax is eluding me. Any help appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop cell showing zero when returning a blank cell | Excel Discussion (Misc queries) | |||
Returning Cell Contents of One Cell Based On Another Cell | Excel Worksheet Functions | |||
Returning Cell Value if someone deletes the contents of a cell | Excel Worksheet Functions | |||
returning a text cell based on a number cell | Excel Worksheet Functions | |||
Need a formula for returning the value of the cell shading in a cell | Excel Programming |