Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you are expecting a <code>String</code>/Text value from your input box then you should specify it,</p> <pre><code>Dim column1 As String 'display an input box asking for column column1 = InputBox("Please enter column to ckeck", "Range to Check", , , , 2) </code></pre> <p>Instead of juggling with a String here, why don't you just use the <code>Range</code> object where user can simply click on either full range or one cell in that column you want to check.. </p> <p>Using a range to get the inputbox data: You main issue seems to be setting range to check column in formula.</p> <pre><code>Option Explicit Sub LookForDuplicates() Dim LastRow As Long, StartRow as Long Dim column1 As Range, column2 As Range Set column1 = Application.InputBox("Please enter _ column to ckeck", "Range to Check", , , , , , 8) If column1 Is Nothing Then MsgBox "No column entered" Exit Sub End If Set column2 = Application.InputBox("Please _ enter column to insert results", _ "Range to Output Results", , , , , , 8) If column2 Is Nothing Then MsgBox "No column entered" Exit Sub End If LastRow = Cells(Rows.Count, column1.Column).End(xlUp).Row '--updated here StartRow = column2.Row '-- here a new code added, assuming that you will have at least one row for column titles With column2 .FormulaR1C1 = "=COUNTIF(R" &amp; column1.Row _ &amp; "C[-1]:R" &amp; LastRow + 2 &amp; "C[-1],RC[-1])" .AutoFill Destination:=column2.Resize(LastRow - StartRow, 1) End With column2.Offset(-1, 0).FormulaR1C1 = "Duplicates" End Sub </code></pre> <p>Output:</p> <p><img src="https://i.stack.imgur.com/ewk5u.jpg" alt="enter image description here"></p>
    singulars
    1. This table or related slice is empty.
    1. POexcel countif with user input variables
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbonCodigo
    1. USbonCodigo
    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. COThanks @bonCodigo I really appreciate you taking the time to help however a range is not really suitable for this. The columns are often very long and the users will struggle with this. I really need to specify a column to check and a column for output.
      singulars
      1. PO
      1. USPaul
    2. COIn the above sample, **you don't have to select the entire column range**. First cell is enough and that's what I have done above. Then the code is finding the last used row (`LastRow = column1.Rows.Count`)in that column instead of you going to search via the entire column and which is a performance matter. I am not sure if you really got what I meant in the above code. Please let me know :)
      singulars
      1. PO
      1. USbonCodigo
    3. COAh yes that makes more sense. I was under the impression you had to select the entire data selection. Thanks so much for your help it is really appreciated. Now if I run that script and select a column of data and then another column to enter the data I get an error 1004 Apllication-defined or object -defined error on line: .FormulaR1C1 = "=COUNTIF(R" & column1.Row & "C[-1]:R" & LastRow + 2 & "C[-1],RC[-1])" I took the _ and new line out just incase....
      singulars
      1. PO
      1. USPaul
 

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