excel-vba,compare,range,msgbox , Compare two sets of Ranges and MsgBox amount and value of common cell values

## Question:

Tag: excel-vba,compare,range,msgbox

I have a problem with this code and I'm not able to resolve it.

I have two sets of ranges; Range("C14:C19") and Range("E14:E19") each containing 6 Numbers generated randomly (I already coded the random selection).

My goal is to, after generating the Numbers, compare both Ranges. If both Ranges contain identical Numbers, I want to MsgBox the amount of identical Numbers found in both Ranges and also the value of each Numbers.

Example: After generating the Numbers, if both Ranges have the values 9 and 7 in common, the MsgBox would look like:

There is/are 2 number(s) in the winning combination and it's/there value(s) is/are : 7, 9

Here Is what I was able to do up to now.

Private Sub Compare()

Dim rgnChoix As Range, rgnTirage As Range, i As Range, j As Range, iVal As Integer

Set rgnChoix = Range("C14:C19")
Set rgnTirage = Range("E14:E19")

For Each i In rgnChoix.Cells

For Each j In rgnTirage.Cells

If i.value = j.value Then

MsgBox "There is/are" & " " & iVal & " " & "number(s) in the winning combination and it's/there value(s) is/are :" & " " & i.value, , "Erika"
Exit Sub

End If
Next j
Next i

iVal = Application.WorksheetFunction.CountIf(Range("C14:C19"),"i.value=j.value")

End Sub


Thanks!! Erika

It may work as you wish. But if same number occurs more than once in a range it counts it as a seperate match. It counts all the matchs seperately. If one range includes two 4 and the other includes three 4 it counts six matching.

Private Sub Compare()
Dim rgnChoix As Range, rgnTirage As Range, i As Range, j As Range, iVal As Integer, sVal As String
Set rgnChoix = Range("C14:C19")
Set rgnTirage = Range("E14:E19")
For Each i In rgnChoix.Cells
For Each j In rgnTirage.Cells
If i.Value = j.Value Then
iVal = iVal + 1
sVal = sVal & "," & CStr(i.Value)
End If
Next j
Next i
sVal = Mid(sVal, 2)
MsgBox "There is/are" & " " & iVal & " " & "number(s) in the winning combination and it's/there value(s) is/are :" & " " & sVal, , "Erika"
End Sub


# Related:

excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

## Way to capture double click to open file?

Is there a way to have an Excel macro to check what file I double clicked on to open. When I open that file, the Add-Ins installed load first, then the file I clicked on loads. How can I write code inside one of my Add-Ins to check what the...

## Compare 2 seperate csv files and write difference to a new csv file - Python 2.7

python,python-2.7,csv,compare
I am trying to compare two csv files in python and save the difference to a third csv file in python 2.7. import csv f1 = open ("olddata/file1.csv") oldFile1 = csv.reader(f1) oldList1 = [] for row in oldFile1: oldList1.append(row) f2 = open ("newdata/file2.csv") oldFile2 = csv.reader(f2) oldList2 = [] for...

## TSQL update value with subquery

sql-server,tsql,sql-update,compare
I have 2 tables and want to compare them and modify tableA (set NameMod = 1) if it has different rows. To compare tables I use: select Id, Name from tableB except select Id, Name from tableA And then I want to modify tableA: update tableA Set NameMod = 1...

## Changing the active cell

excel,vba,excel-vba,excel-2007

## VBA - .find printing wrong value

excel,vba,excel-vba
I have two sections of code that basically do the same thing but with two different columns. The code finds the header "CUTTING TOOL" and "HOLDER" (looping through multiple files) and prints the information from those columns into one worksheet, masterfile. I was using a less efficient method of setting...

## Replace reference with its value in Excel VBA workbook

vba,excel-vba
I have an Excel workbook to manage my Delivery Notes, which creates another workbook with actual delivery note and stores it in the folder for me, then places new record in my main workbook so that the Delivery Number for the next delivery note can be increased by 1. My...

## How do I store a SQL statement into a variable

sql,excel,excel-vba
I am currently facing a problem here. I have a column called "DESC1" in a table called "Master". I'm trying to retrieve the value based on something along the lines of this... "Select DESC1 FROM Master WHERE '" & TextBox1.Text & "' " And I'm trying to display on the...

## VBA how to initialize vCPath

vba,excel-vba,excel-2010
How do I initialize vCPath? VBA Run-Time Error 1004 @Garry's Student says I "must somehow use the info you get from Application.FileDialog(msoFileDialogOpen) to generate the full filespec of the file you wish to open". What's the simplest way to do this? I am a VBA beginner: I have been programming...

## Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

## ReplaceLine method in VBE only replacing part of line

excel-vba,vbe
In response to this question I thought it would be fun to write a VBE macro that would automatically replace lines which look like DimAll a, b, c, d As Integer by Dim a As Integer, b As Integer, c As Integer, d As Integer In my first draft I...

## Efficient comparison of small integer vectors

c,integer,compare,bit-manipulation,string-comparison
I have small vectors. Each of them is made of 10 integers which are between 0 and 15. This means that every element in a vector can be written using 4 bits. Hence I can concatenate my vector elements and store the whole vector in a single long type (in...