[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Validation in Excel

Posted on 2011-05-10
10
Medium Priority
?
450 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:nainil
  • 4
  • 4
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35736493
For the given scenario what should the message be?

What are the possible variations which can be expected?
0
 

Author Comment

by:nainil
ID: 35736522
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.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 35752539
Try this macro
Sub valdat2sheets()
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
nr1 = Application.CountIf(sh1.Range("C:C"), "ST")
nr2 = Application.CountIf(sh2.Range("C:C"), "ST")
If nr1 > nr2 Then
MsgBox "Sheet1 (" & nr1 & ") has more rows than Sheet2 (" & nr2 & ")"
ElseIf nr1 < nr2 Then
MsgBox "Sheet2 (" & nr2 & ") has more rows than Sheet1 (" & nr1 & ")"
End If
rwlist = ""
For Each rw1 In sh1.Range("A2:C" & Range("A2").End(xlDown).Row).Rows
If rw1.Cells(1, 3) = "ST" Then
    For Each rw2 In sh2.Range("A2:C" & Range("A2").End(xlDown).Row).Rows
    rwmatch = True
    For i = 1 To 3
    If rw1.Cells(1, i) <> rw2.Cells(1, i) Then rwmatch = False
    Next i
    If rwmatch = True Then Exit For
    Next rw2
    If rwmatch = False Then rwlist = rwlist & rw1.Row & " "
End If
Next rw1
If rwlist <> "" Then MsgBox "The following rows on Sheet 1 were not found on sheet2:" & vbCrLf & rwlist
rwlist = ""
For Each rw2 In sh2.Range("A2:C" & Range("A2").End(xlDown).Row).Rows
If rw2.Cells(1, 3) = "ST" Then
    For Each rw1 In sh1.Range("A2:C" & Range("A2").End(xlDown).Row).Rows
    rwmatch = True
    For i = 1 To 3
    If rw1.Cells(1, i) <> rw2.Cells(1, i) Then rwmatch = False
    Next i
    If rwmatch = True Then Exit For
    Next rw1
    If rwmatch = False Then rwlist = rwlist & rw2.Row & " "
End If
Next rw2
If rwlist <> "" Then MsgBox "The following rows on Sheet2 were not found on Sheet1:" & vbCrLf & rwlist
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:nainil
ID: 35756002
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)....
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35756988
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.
0
 

Author Comment

by:nainil
ID: 35760009

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:
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35763870
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
0
 

Author Closing Comment

by:nainil
ID: 35764015
I see how this works now.

It works for me. Appreciate your help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Progress

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question