Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Comparing data in different formats
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Comparing data in different formats
Resolved · Medium Priority · Version 2010
Reply Tue 1 Mar 2011, 11:03Delegate Jemima said...
Jemima has attended:
Excel VBA Intro Intermediate course
Comparing data in different formats
I need to compare data from two different sources that are in different formats - I basically have the date in the format dd/mm/yyyy and the date in dd/mm/yyyy hh:mm:ss.
I need to check if these match, along with two other variables and then output the data onto a new sheet (bearing in mind the two other variables are text and may also have slight differences).
I have inherited the code below which I think does what I am trying to achieve but there is no explanation with it, and as I am relatively new to VBA it's like trying to figure out another language. Any help/guidance would be appreciated!
If StrComp(GSMCell(i, cGSMHomeTeamName), Mid(RBCell(j, cRBHomeTeamName), 1, Len(RBCell(j, cRBHomeTeamName)) - 3), vbTextCompare) = 0 And StrComp(GSMCell(i, cGSMAwayTeamName), Mid(RBCell(j, cRBAwayTeamName), 1, Len(RBCell(j, cRBAwayTeamName)) - 3), vbTextCompare) = 0 Then
rbDate = CDate(Left(RBCell(j, cRBMatchDate), 10))
If (Abs(rbDate - gsmDate) < 1.5) Then
rbHomeScore = CInt(RBCell(j, cRBHomeTeamScore))
rbAwayScore = CInt(RBCell(j, cRBAwayTeamScore))
If gsmHomeScore = rbHomeScore And gsmAwayScore = rbAwayScore Then
checkFlag = 0
Else
checkFlag = 1
End If
matchFlag = 1
Exit For
End If
For upcoming training course dates see: Pricing & availability
Reply Fri 18 Mar 2011, 11:49Delegate Jemima said...
RE: Comparing data in different formats
Sorry, just another question that's quite similar to the above...
I need to compare the text that is in Columns B and C on Sheet 1, with text that is is columns C and E on Sheet 2. However, as the text between the two sheets is in a different format (they may have 2 or 3 extra letters at the beginning or end of the main text), i need to somehow cut these to make the text comparable.
If these are a match, I then need to copy the data from sheet 2 to sheet 1 but nothing I've tried seems to work correctly.
Hopefully this clarifies the above a little!
Thank you
Jemima
Reply Fri 10 Jun 2011, 11:45
Edited on Fri 10 Jun 2011, 11:48Delegate Pedro said...
RE: Comparing data in different formats
Hi Jemima
I'm trying to decipher your code :-)
And even If I don't undestand exactly the first string comparation I found what it looks like a big mistake at the end of it:
Where it says Exit For it looks like it should be End IF
Is this the whole code or there is other part that you didn't copied?
There are three IFs and only 2 End Ifs
so the first thing should be to change this Exit For for End IF and check if works.
If StrComp(GSMCell(i, cGSMHomeTeamName), Mid(RBCell(j, cRBHomeTeamName), 1, Len(RBCell(j, cRBHomeTeamName)) - 3), vbTextCompare) = 0
And StrComp(GSMCell(i, cGSMAwayTeamName), Mid(RBCell(j, cRBAwayTeamName), 1, Len(RBCell(j, cRBAwayTeamName)) - 3), vbTextCompare) = 0 Then
rbDate = CDate(Left(RBCell(j, cRBMatchDate), 10))
If (Abs(rbDate - gsmDate) < 1.5) Then
rbHomeScore = CInt(RBCell(j, cRBHomeTeamScore))
rbAwayScore = CInt(RBCell(j, cRBAwayTeamScore))
If gsmHomeScore = rbHomeScore And gsmAwayScore = rbAwayScore Then
checkFlag = 0
Else
checkFlag = 1
End If
matchFlag = 1
Exit For
End If
Regards,
Pedro F. Pardo
|