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:

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...

adding variables into another variable vba

excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

Range, Select, Change contents, Allignment or Offset?

excel,vba,excel-vba
I have a case at the moment where I am moving down the column with the names below and clicking on a macro, that then marks the indicator with a 35, a few columns down to the right. Due to the nature of the page, I am wanting to count...

Retrieve Number from a website into Excel

excel,excel-vba
From this website http://bit.ly/1Ib8IhP I am trying to get this number into an Excel cell. Avg. asking price in Bayswater Road: £1,828,502 Is there any way using VBA or another tool? Couldn't make it work with a web query....

Excel - Open a named userform

excel-vba,userform
Sure this is very basic, I had userforms that I had not named and I called these by using UserForm1.Show. Now I have named these eg. ProgressBar. I call this one by UserForms.Add("ProgressBar").Show vbModeless But i cant figure out how to make this bit work: Private Sub UserForm_Initialize() 'With UserForms("ProgressBar")...

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...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Excel-VBA: create named Range in row until end of cell content

vba,excel-vba,range
Imagine an Excel sheet with some rows and some content in each row (i.e. different column-length for each row). In Excel-VBA: How can I create a range-X within column-X that goes from row-cell-2 to the end of the content of this X-column ?? i.e. I would like to create a...

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...

Double compare in JavaScript looks weird [duplicate]

javascript,boolean,compare,boolean-expression
This question already has an answer here: chaining double equals == returns false 3 answers Let's var a = "first", b = "second", c = "first";. Expression a == c returns true, (of course!); a == c is true too. Then why does a == a == c return...

Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

How to insert data by using VBA in excel

excel-vba
I have 3 sheets (sheet1,sheet2,sheet3) in sheet1 has all user ID, sheet2 has logon user ID and sheet3 is empty. The point is... I need to put not logon user ID into sheet3 but my code fail. If it's a stupid question that because I'm newbie with VBA Here my...

Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format
I am in process of creating a macro that will save the current workbook, create a new outlook message and attach the file to the message. My macro does that but I can not format the text in the body of the email to my liking. Dim OutApp As Object...

vba,excel-vba
Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i...

Vba changing directory to save

excel-vba
I'd like to save a file in a directory whose name changes according to the name of the Previous Month. Here is the code: name_month = MonthName(Month(Date) - 1) Set Newbook = Workbooks.Add With Newbook .Title = "TESO1" .SaveAs Filename:="C:\Users\ee31264\Desktop\Mensile Automat\name_month \send\TESO1.xlsx" End With Newbook.Close unfortunately the name_month i wrote...

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...

If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

Date formatting - making dd/m/yyyy into dd/mm/yyyy

excel-vba,date,format
I got a pretty simple question (but yet I've been stuck at it for some time now). Does anyone know how to make the date value from dd/m/yyyy into dd/mm/yyyy in a variable? dim lastdaylastmonth as date lastdaylastmonth = DateSerial(Year(Date), Month(Date), 0) So this code, as of now, would return...

Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

Range.offset propety

excel-vba
I'm trying to understand a code on VBA, to debug the VBA program of someone. So I want to know that is the property and the meaning of Range (Value).offset(x;y). Here is my code: `Sub initialise() Dim i As Double, j As Double For i = 0 To maxnodes -...

Exit Sub And Call another Sub

vba,excel-vba,call
I have two subs, sub1 and sub2. I want to exit sub1 completely and start sub2 if a condition is satisfied. My attempt is (running from : sub1 . . If x=y Then Exit Sub And Call sub2 End If . . End Sub ...

Extract All Named Ranges Into A Class

vba,excel-vba
I have a workbook with a very large amount of named ranges (well over 200). I really need a way to work quickly and easily with all of the named ranges so I can then work with / populate them using VBA. My solution up until now has been to...

Changing the active cell

excel,vba,excel-vba,excel-2007

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...

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...

Synchronize local SQLite database with central database

android,performance,sqlite,compare
We are developing an Android App based on a product that already has a website. As a result, when launching the Android app it may happen that a lot of things differ between the local SQLite database and our central database. What is the fastest way to synchronize the Android...

Using a stored integer as a cell reference

excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

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...

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...

How do I get a cell's position within a range?

excel,vba,excel-vba
How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the Row- and Column-properties, but I am unsure of how to do the same within a range. I considered using the position of...

Best way to return data from multiple columns into one row?

excel,vba,excel-vba
I have a sheet with just order numbers and another with order numbers and all of the data associated with those order numbers. I want to match the order numbers and transfer all of the available data into the other sheet. I've been trying to use loops and VLOOKUP but...

Python - Using a created list as a parameter

python,list,loops,if-statement,compare
When I run my code it tells me: Type Error: unorderable types: str() < float(). I can't figure out why it won't let me compare these two numbers. The list I am using is defined, and the numbers in it have been redefined as floats, so I'm not sure what...

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...