StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel Do While Find
text
Body
copied!<p>Below is my code that uses .Find function in excel to find the location where dodCell appears in sheet2 and to add reeCell to column 18 in sheet2 from sheet1. This is suppose to loop based on how many times it finds strSearch in rRange. </p> <p>But, it currently it is only running once and stops, I think there is a mistake in my "Do While Loop" but I can't seen to fix it.</p> <p>Any thoughts?</p> <p>So after fixing some errors that were pointed out in my code I have revised the sub. I think I have addressed the Loop issue, but know the program is running once and is freezing excel and then I need to restart Excel. I think I have created an infinite loop but don't know how to fix it any ideas?</p> <p><pre><code> Sub addnumber() 'used to add ree value to Dod projects Dim sSht As Worksheet, dSht As Worksheet Dim lastrow As Integer Dim firstAddress As String Dim strSearch As String Dim ReeCell As Range, dodCell As Range, aRange As Range, rRange As Range, aaRange As Range Dim hold1Cell As Range, holdCell As Range, lastCell As Range Set sSht = Worksheets("Sheet1") Set dSht = Worksheets("Sheet2") Set rRange = sSht.Columns(18) Set aRange = sSht.Columns(1) Set aaRange = dSht.Columns(1) lastrow = sSht.Range("A" & Rows.Count).End(xlUp).Row strSearch = "2*"</p> <pre><code> Set dodCell = rRange.Find(What:=strSearch, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'If something dodCell holds a value then enter loop If Not dodCell Is Nothing Then 'Set lastCell to dodCell firstAddress = dodCell.Address Do 'Set ReeCell to the value of the Ree number Set ReeCell = dodCell.Offset(0, -17) 'Set holdCell to the Cell that holds that Dod number in "Sheet2" Set holdCell = aaRange.Find(What:=dodCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'Set hold1Cell to the location that the Ree Value should be Set hold1Cell = holdCell.Offset(0, 9) 'Give hold1Cell the Ree # from ReeCell hold1Cell = ReeCell.Value Set dodCell = rRange.FindNext(dodCell) Loop While Not dodCell Is Nothing And dodCell.Address <> firstAddress End If End Sub </code></pre> <p></pre></code></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload