asked on

VBA Validation in Excel

I have two sheets.

I need to validate the following:
a. Number of rows in sheet1 should be equal to number of rows in sheet 2 for type='ST'

Sheets("1").Range("A:A").AutoFilter Field:=1, Criteria1:="ST"
    Sheets("1").AutoFilter.Range.Copy Destination:=Sheets("1").Range("B1")
    UniqueCountST1 = Sheets("1").Range("B:B").End(xlDown).Row - 1

    UniqueCountST2 = Sheets("2").Range("B:B").End(xlDown).Row - 1
    
    If UniqueCountST1 <> UniqueCountST2 Then
        MsgBox ("The Count of rows with FieldType ST in 1 sheet should match the number of rows in 2 sheet.")
    End If

Open in new window

b. If the count is equal, the XCode and RCode Column should be same in both the sheets.

Sheet 1

RCode	XCode	Type
A	B	ST
O	B	ST
R	S	ST
M	B	FT
B	X	DD
K	B	ST
L	B	ST
S	B	ST
D	B	ST

Open in new window

Sheet 2

RCode	XCode	FORMAT
O	B	ST
R	S	ST
K	B	ST
L	B	ST
S	B	ST

Open in new window

I am looking for VBA code with the second part of the validation, probably extending the part A validation.

Saqib Husain

For the given scenario what should the message be?

What are the possible variations which can be expected?

nainil

ASKER

There is a high possibility that the items will not be ordered in the correct way. So, we would need to sort them and then initiate the comparison.

If there is a mismatch, a popup can be shown identifying the problem points.

A couple of variations I can identify:
a. number of rows in Sheet 1 with type='ST" > Number of rows in Sheet 2
b. number of rows in Sheet 1 with type='ST" < Number of rows in Sheet 2
c. number of rows in Sheet 1 with type='ST" = Number of rows in Sheet 2, HOWEVER, the contents in RCode and XCode are different.

Hope this helps answer your question.

Thanks in advance for your help.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nainil

ASKER

ssaqibh: This is terrific code.
It worked, no issues.

I am wondering if we can use Ranges instead of the Cells (1,i) option... I have always seen it an issue to comprehend the Cells references. (probably a small example will help)....

Saqib Husain

I am not too sure about this. I think since we are looping through columns (as in line 17) the best choice would be to use the cells. Ranges would be looking for column alphabets which would make it more complicated.

nainil

ASKER

Not sure if this helps to get the ColumnNames:

Function GetColumnName(MyColumnID As Integer) As String
    Dim MyColumnLetter As Variant
        
    MyColumnLetter = GetColumnLetter(MyColumnID)
    
    GetColumnName = ActiveSheet.Range(MyColumnLetter & 1)

End Function
Function GetColumnLetter(ColumnID As Integer)
    GetColumnLetter = Chr(ColumnID + 64)
End Function

Open in new window

Can you please share some insights on how we read the Cells (1,i) counter?

Thanks ssaqibh:

Saqib Husain

cells(1,i) is row 1 column i of the spreadsheet

but in case of

rw1.Cells(1, i) it gives row 1 column i of rw1

So basically we need to know which row is rw1 which can be known be rw1.row

nainil

ASKER

I see how this works now.

It works for me. Appreciate your help.