Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There a couple of general comments I would suggest thinking about to start off with, though they may not solve the problem. I must admit I haven't downloaded your workbook so apologies if you've already done this in bits of code you haven't posted.</p> <p>a. It's probably good rigor to start the module with <code>Option Explicit</code>. From the code posted here, it looks like you introduce an un-<code>Dim</code>ed variable, <code>transfer</code>, right where the problem starts. You also seem to create a variable <code>EntryRow</code> - possibly you mean the EntireRow function? Here's a piece of code demonstrating the need for <code>Option Explicit</code></p> <pre><code>Dim existingQuote As Variant transfer = xlCell.Address Dim existQuote As existQuote = transfer Dim existingRow As Integer existingRow = existingQuote.Row </code></pre> <p>You <code>Dim</code>ed the variant <code>existingQuote</code>, created an un-<code>Dim</code>ed variable <code>transfer</code>, <code>Dim</code>ed <code>existQuote</code> without data type, and then try to assign the value of the variable <code>existingRow</code> to <code>existingQuote.row</code> without <code>Set</code>ing what the <code>existingQuote</code> variant array variable should contain.</p> <p>b. Why not go straight to stuff? If you want to select cell A2 in <code>importWS</code> and do something to it/get something from it, what about</p> <pre><code>valueToFind = importWS.Range("A2").Value </code></pre> <p>This at least may help make the code easier to read, although I have the (possibly superstitious) feeling that the macros also seem a little bit faster </p> <p>c. I would also recommend destroying variables at the end of a subroutine.</p> <p>If you clear up your code, it may also help the forum boffins to get to grips with what you're trying to do.</p> <p><strong>EDIT</strong>: (Untested)</p> <p>How about this:</p> <pre><code>Dim xlCell As Excel.Range Dim existQuote As Long For Each xlCell In checkRange If xlCell.Value = valueToFind Then existQuote = xlCell.Row MsgBox (valueToFind &amp; " in row " &amp; existQuote &amp; " has been converted to an order", "Order Conversion Alert: ") End If Next xlCell Loop Until ActiveCell.Value &gt; 300000 Or ActiveCell.Value = "" </code></pre> <p>Note: This will only work if there are no gaps between data filled cells in the column <code>ActiveCell</code> traverses during the <code>Do</code> loop. If you have gaps you might want to get a loop-end value from the last row on the sheet with data instead. You could do that like this:</p> <pre><code>Dim wb As Workbook Set wb = ActiveWorkbook Dim wS As Worksheet Set wS = ActiveSheet Dim importWS as worksheet Dim dataend As Long Dim counter As Long importWS = sheets.("NEWDATA") select.importWS dataend = Range("A65000").End(xlUp).Row Range("A1").Select Do ActiveCell.Offset(1, 0).Select If ActiveCell.Value = "" Then GoTo newloop Dim valueToFind As Long valueToFind = ActiveCell.Value Dim checkRange As Range Set checkRange = wS.Range("D1:D" &amp; EntryRow) Dim xlCell As Excel.Range Dim existQuote As Long For Each xlCell In checkRange If xlCell.Value = valueToFind Then existQuote = xlCell.Row MsgBox (valueToFind &amp; " in row " &amp; existQuote &amp; " has been converted to an order", "Order Conversion Alert: ") End If Next xlCell newloop: counter = counter + 1 Loop Until ActiveCell.Value &gt; 300000 Or counter = dataend ActiveCell.Offset(-1, 0).Select Dim DataPoints As Integer DataPoints = ActiveCell.Row ActiveCell.Offset(1, 0).Select </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POIdentifying duplicate values across two worksheets
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USOrphid
    1. USOrphid
    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. COa) I've now clarified what I was originally trying to do. I cannot seem to get `xlCell.address` into a variable, that's my primary issue here. b) This snippet is part of a loop which separates quotes (2*****) from orders (3*****), hence the use of the offset function. I tried to include only the relevant code but it seems like this may have obfuscated my code. c) I was under the impression that variables were automatically destroyed at the end of a sub unless they were declared as public or global?
      singulars
      1. PO
      1. USCassiopeia
    2. COOK, so do you get an error message at all? Since you've `Dim`ed existQuote as a `Range` don't you need you need to `Set` it to an array? And is .Address an array or a string anyway? From the help file it looks like .Address returns a string value. Try using `.Address(RowAbsolute)` as well. You've still got `existingQuote` as an un-`set` variable too. How does VBA know the content of `existingQuote`, as opposed to the `Dim`ed `existQuote`? You're right about destroying variables, by the way, but I always like to see the command visibly and it can be good practice for other languages.
      singulars
      1. PO
      1. USOrphid
    3. COOne more thing - I wonder: If you don't need to display the full address to the user in the `MsgBox`, why not just use `Dim existQuote As Long, [linebreak] existQuote = xlCell.Row [linebreak] Debug.Print existQuote [linebreak] Exit Sub` See if that picks up the value you need?
      singulars
      1. PO
      1. USOrphid
 

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