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

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

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

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

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

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

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