Databases
StackOverflow2013
Postsdbo
POExcel - VBA : Match with two combined cells?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel - VBA : Match with two combined cells?
    primarykey
    data
    text
    <p>my question is quite tricky so I'll try to make it as clear as possible.</p> <p>In my program, I compare cells value with a database in order to find matches. The cells I am comparing are under a column format, with one word in each cell. The cells from my database, on the other hand, contain a whole sentence in a cell. </p> <p><strong>For example</strong>: <img src="https://i.stack.imgur.com/xtOIa.jpg" alt="enter image description here"></p> <p>My program's goal is to find matches to identify a place. So far, here is what it is doing: it activates cell from the database and compare with each cell of "elementsListRange" which is a selection of elements from the column (so in my picture "B2:B7"). When there is a match, it adds a score of 12 on the line. </p> <pre><code> 'loop in the DATABASE For i = a To b Range("B" &amp; i).Activate 'try to find a match btwn active cell and one of the elements from parsed address For Each cell In elementsListRange.Cells If Match(ActiveCell.Value, cell.Value) Then Range("K" &amp; i).Value = Range("K" &amp; i).Value + 12 Else Range("K" &amp; i).Value = Range("K" &amp; i).Value + 0 End If Next </code></pre> <p>So in this case, there is a match three times, with cell 1 (corporate), cell 2 (center) and cell 3 (building), which makes a total of 36 points.</p> <p><strong>Here is what I want to do :</strong> now, I want to make it more accurate by taking into account the position of words. So if the cell in the database is "Corporate Center Building", and the words Corporate/Center and Building appear in the same order, it would add more points. The idea is : If there is a match between active cell and cell 1 (Corporate), Then add 12 points and do another check : If match between active cell and cell 1 + cell 2 (Corporate Center), then add 10 additional points and do another If: If match between active cell and cell 1 + cell 2 + cell 3 (Corporate Center Building), add 15 additional point, and so on.</p> <p>So it won't only find matches, but it will give importance to the position of words. So if the input is "Corporate Center Building" and you have both "Corporate Center Building" and "Building Corporate Center" in the database, the right match will be given more points. </p> <p><strong>Something like this :</strong> </p> <pre><code>'loop in the DATABASE For i = a To b Range("B" &amp; i).Activate 'try to find a match btwn active cell and one of the elements from parsed address For Each cell In elementsListRange.Cells If Match(ActiveCell.Value, cell.Value) Then Range("K" &amp; i).Value = Range("K" &amp; i).Value + 12 If Match(ActiveCell.Value, cell.Value + cell.offest(1,0).Value) Then Range("K"&amp;i).Value = Range("K"&amp;i).Value + 10 If Match(ActiveCell.Value, cell.Value + cell.offest(1,0).Value + cell.offset(2,0).Value) Then .... ..... ..... + 15 and so on Else End If Else End If Else Range("K" &amp; i).Value = Range("K" &amp; i).Value + 0 End If Next </code></pre> <p><strong>And finally, here is my question:</strong></p> <p>Unfortunately, we can't write this this way : </p> <pre><code> If Match(ActiveCell.Value, cell.Value + cell.offest(1,0).Value) </code></pre> <p>Do you know how to express this idea ? Thanks in advance.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPhalanx
    1. USPhalanx
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel - VBA : Match with two combined cells?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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