Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would just filter your data in place using the built in Excel Filters, then copy the results over instead of trying to Loop over every row. </p> <p><em><strong>BUT</em></strong> If you want to loop the rows anyways:</p> <p>In order to use the <code>Range</code> function you need to use column letters not column numbers. </p> <p>You have 2 options here. Use </p> <pre><code>Chr(lastcolumn1 + 64) </code></pre> <p>instead of lastcolumn1. The flaw is This will only work for columns up to columns <code>Z</code>, and it won't work for double letter columns without an if statement and more code. Like the following should work for up to Column <code>ZZZ</code></p> <pre><code>If lastcolumn1&gt; 52 Then strColumnLetter = Chr(Int((lastcolumn1- 1) / 52) + 64) &amp; Chr(Int((lastcolumn1- 27) / 26) + 64) &amp; Chr(Int((lastcolumn1- 27) Mod 26) + 65) ElseIf lastcolumn1&gt; 26 Then strColumnLetter = Chr(Int((lastcolumn1- 1) / 26) + 64) &amp; Chr(Int((lastcolumn1- 1) Mod 26) + 65) Else strColumnLetter = Chr(lastcolumn1+ 64) End If </code></pre> <p>But you could also use</p> <pre><code>strColumnLetter = Split(Cells(1, lastcolumn1).EntireColumn.Address(False, False), ":")(0) </code></pre> <p>OR</p> <pre><code>strColumnLetter = Left(Replace(Cells(1, lastcolumn1).Address(1, 0), "$", ""), InStr(1, Replace(Cells(1, lastcolumn1).Address(1, 0), "$", ""), 1) - 1) </code></pre> <p>OR </p> <pre><code>strColumnLetter = Left(Cells(1, lastcolumn1).Address(1, 0), InStr(1, Cells(1, lastcolumn1).Address(1, 0), "$") - 1) </code></pre> <p>as that will work for as many columns as Excel will hold.</p> <p>Your last option if you don't want to convert the number to the column Letter would be to get a range of Cells, as the <code>Cells</code> function CAN accept column numbers for arguments. </p> <pre><code>sh.Range(cells(L,1), cells(L,lastcolumn1)) </code></pre> <p>Again I would suggest just using the standard built in filter function to filter out the data you don't want then just copy whats left though. This was just to add more options. </p> <p>If you supply some sample info I could write you a sub that will do the filter copy paste for you but I don't know how your data is set up. </p> <p>here is an example that should work based on your Original Question: </p> <pre><code>Sub FilterAndCopy() Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Dim sh As Worksheet, sh2 As Worksheet Dim lastrow1 As Long Dim lastcolumn1 As Long Dim Distance As Long Distance = 14 Set sh = ThisWorkbook.Sheets("Sample Address Database") Set sh2 = ThisWorkbook.Sheets("Workspace") lastrow1 = sh.Cells(Rows.Count, "A").End(xlUp).Row lastcolumn1 = sh.Cells(1, Columns.Count).End(xlToLeft).Column With sh .Range(.Cells(1, 1), .Cells(lastrow1, lastcolumn1)).AutoFilter , _ field:=Distance, _ Criteria1:="&lt;=" &amp; CDbl(151), _ Operator:=xlAnd .Range(.Cells(2, 1), .Cells(lastrow1, lastcolumn1)).Copy _ sh2.Range("A2") End With Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCopying Rows in For Loop and Pasting to new Sheet
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USuser2140261
    1. USuser2140261
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCopying Rows in For Loop and Pasting to new Sheet
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI was able to use Ed Heywood-Lonsdale's advice but the filter idea is interesting as I'm always looking to streamline code. Here's the thing, though; I have a large database of information and I'm copying it to another sheet if it matches the right criteria. Then I'm sorting that information to keep the original database intact and to speed up searching.
      singulars
      1. PO
      1. USAl.Sal
    2. CO@Al.Sal I have updated my answer with an example of how you would implement the Filter~>Copy~>Paste Let me know if there are any issues. Remember always run code on a COPY of your data.
      singulars
      1. PO
      1. USuser2140261
    3. CO@Al.Sal What exact line is it Highlighting when you click Debug?
      singulars
      1. PO
      1. USuser2140261
 

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