I have been using this code, but now I believe it is deleting the row instead of moving the text one cell down!
I need to find these numbers in the array (part) in the row D and move the text down one row. If it finds 63110 in D3, move it to D4. HELP!??
Worksheets("Sheet1").Activate Set Ws = ActiveSheet With Ws varArray = Array("63110", "63120", "63130", "63140", "63150", "63100", "63395", "66045") For lngEntry = 0 To UBound(varArray) Set rngFound = .Cells.Find(What:=varArray(lngEntry), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False) If Not rngFound Is Nothing Then strSave = rngFound lngRow = rngFound.Row .Cells(lngRow, "D").EntireRow.Delete .Cells(lngRow, "D") = strSave End If Next End WithEnd Sub
Yes, it deletes the row and writes the content into the next following one (what is the same at the end). You may work with offset. If you have the current Rage Object of the found cell, you can use Rage.Offset(row,column) to refer to a cell, which is relative (rows, columns) to the current object... Range("D3").Offset(1,0) is the field under D3 = D4
Euro5
ASKER
I am getting an error at strSave = ringFound.Offset(1, 0)
Sub move()Dim rngFound As RangeDim ws As WorksheetDim varArray As VariantDim lngEntry As LongDim strSave As StringDim lngRow As LongDim downRow As String Worksheets("Sheet1").Activate Set ws = ActiveSheet With ws varArray = Array("60200", "62200", "62500", "66000") For lngEntry = 0 To UBound(varArray) Set rngFound = .Cells.Find(What:=varArray(lngEntry), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False) If Not rngFound Is Nothing Then strSave = rngFound lngRow = rngFound.Row strSave = ringFound.Offset(1, 0) End If Next End WithEnd Sub
Yes, just a typo in the first code, as "ringFound" is not defined, and even in the wrong direction.
Let me add the comment, that it is usual to accept the solution of the expert, which gave you the hint to solve your problem. This is just the honor for the experts here, whichs takes the time to answer your question.
You accepted yourself as solutions, what means you ignore the experts help. Doing this may have the effect, that some of the experts will ignore you in the future.
Sorry, Bembi , you are right. I closed that in haste!
I see that I didn't capture all of the code - and even revised it a little more to rngFound.Clear
So the full solution relied your suggestion WAS the key to getting it to work!
Thanks for pointing this out!
You may work with offset. If you have the current Rage Object of the found cell, you can use Rage.Offset(row,column) to refer to a cell, which is relative (rows, columns) to the current object...
Range("D3").Offset(1,0) is the field under D3 = D4