Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>APrough is correct that the do loop does not contain all the parts you want in it.</p> <p>It is not clear to me what you want the do loop to do, but as it stands the do loop does nothing but end automatically during its first loop: either when <code>dodCell.Address = lastCell.Address</code> (Exit Do) or at the start of the next loop, since all other instances of dodCell set <code>ExitLoop = True</code>.</p> <p>To get the loop to do anything (other than end automatically) you need to include other code in it. For example your Set statements, just above the start of the Do Loop, seem like items you may intend to iterate and may want to move into the do loop.</p> <p><strong>Additional comments</strong></p> <p>Your new code does appear to move the appropriate items into the do Loop. However, it looks to me like nesting <code>.Find</code> methods, as you have done, resets the register for active <code>.Find</code> instances. </p> <p>That means that when you initiat the <code>holdCell = dSht.Find</code> the register for <code>dodCell</code> is reset. On <code>.FindNext</code> the same <code>dodCell</code> is found and the <code>firstAddress</code> will match the <code>dodCell</code>, ending hte do loop after only one iteration.</p> <p>The solution I came up with to allow the do loop to run through the entire search range involves the use of a <code>For Each</code> statement that loops through the<code>holdCell</code> values and finds matches to the current <code>dodCell</code>. There are more efficient ways of doing this, but the following code should work as a start. It will be slow if there are very many rows: it loops through every row on Sheet2 for every dodCell value found.</p> <p>Here's my code: </p> <pre><code>Sub AddNumber() 'used to add ree value to Dod projects 'create variables Dim wSht1 As Worksheet Dim wSht2 As Worksheet Dim sFirstrDodCell As String Dim sSearch As String Dim rDodRange As Range Dim rHoldRange As Range Dim rDodCell As Range Dim rHoldCell As Range 'establish initial variable values Set wSht1 = Worksheets("Sheet1") Set wSht2 = Worksheets("Sheet2") wSht1.Activate Set rDodRange = wSht1.Range("R1", Range("R" &amp; Rows.Count).End(xlUp)) wSht2.Activate Set rHoldRange = wSht2.Range("A1", Range("A" &amp; Rows.Count).End(xlUp)) sSearch = "2*" 'find rDodCell values Set rDodCell = rDodRange.Find(What:=sSearch, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'If something rDodCell holds a value then enter loop to find Sheet2 matches If Not rDodCell Is Nothing Then 'Set sFirstrDodCell to first rDodCell sFirstrDodCell = rDodCell.Address 'Apply rDodCell values on Sheet2 Do For Each rHoldCell In rHoldRange If rHoldCell.Value = rDodCell.Value Then rHoldCell.Offset(0, 9).Value = rDodCell.Offset(0, -17) End If Next Set rDodCell = rDodRange.FindNext(rDodCell) Loop While Not rDodCell Is Nothing And rDodCell.Address &lt;&gt; sFirstrDodCell End If End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POExcel Do While Find
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USInstant Breakfast
    1. USInstant Breakfast
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI Karl I have added some revised code in an edit, I'm still having difficulty make my code loop more then once.
      singulars
      1. PO
      1. USTaylor Zwick
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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