MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help on some code - like an "if" statement

  Asked By: Brayden    Date: Aug 28    Category: MS Office    Views: 2251

This is my first post so, hi everyone!!

Ok, I'm a relatively new vba user - have done some simple stuff before
but it was a LONG time ago and I have forgotten most of it.


I'm using Excel 2002 and I'm entering file numbers and client
information which I want to be able to sort different ways (eg alpha
or matter no. etc etc). The sort part is easy and I have created 4
macros and attached them to 4 seperate buttons for this purpose. That
part works fine! Essentially, I want excel to bring up a message box
if it can find that a file number has been enetered twice. I have
tried and tried but can't seem to get my version to work.

Before I tried some programming, I first went down the track of a
COUNTIF statement and asked it to look up the complete range of file
numbers and return a "2" instead of a "1" in a certain cell if the
number appeared more than once. Then, all I thought I needed was an
IF statement to say, IF cell H7=2, add a message box to say "number
entered twice".

Unfortunately I don't THINK you can add a message box to a function in
this way, so I tried vba. Below is my "effort"

Private Sub CommandButton1_Click()
Call viewbydate
If Cells(h8) = Cells(h7) Then
MsgBox "A matter number has been recorded twice"
End If
End Sub

I know, its crap! Just to see if I was heading in the right direction
I took out the else line and ran the macro - it worked! But I don't
need a message box to appear when everything is right, only when wrong.

Whenever I run the above, line 3 of this code gets highlighted so I
imagine I'm missing something - probably obvious to you - I can see
you sitting there going "you dick head!"

Anyway, your assistance would be wonderful.



3 Answers Found

Answer #1    Answered By: Corey Brown     Answered On: Aug 28

Private Sub CommandButton1_Click()
Call viewbydate
If Cells(h8) <> Cells(h7) Then MsgBox "A matter number has been
recorded twice"
End Sub

Answer #2    Answered By: Fred Hicks     Answered On: Aug 28

How about using an Advanced Filter? It can hide all the rows of data except
those with duplicate file numbers.

To use an advanced filter, you must have 3 or more blank rows above your data.
To illustrate, assume your column headings are in row 4 and your data is in rows
5 through 150. File number is in column A.

Put the filter criteria in cells A1:A2 (doesn't have to be in column A).
Normally A1 would repeat the headings of the column you want to filter, but
because we are going to use a formula in our criteria, leave A1 blank. In A2,
enter =COUNTIF($A$5:$A$150,A5)>1 . It should return FALSE. It's important
that the first argument is an absolute range ($A$5:$A$150) and the second is a
relative reference (A5).
To run the advanced filter, click on any cell in your data. Select Filter >>
Advanced Filter... from the Data menu. The filter dialog should figure out the
correct list range; you need to tell it the criteria range ($A$1:$A$2). Don't
select unique records only. Click OK.

All rows will be hidden EXCEPT those with duplicate file numbers (If there are
no duplicate file numbers, ALL the rows will be hidden.) After you investigate
the duplicates, you can unhide the other rows by selecting Filter >> Show All
from the Data menu.

All this can be automated with a macro, as follows:

Sub FindDuplicates()
Dim Rng As Range, msg1 As String
On Error GoTo FDerr1
Set Rng = Selection
ActiveSheet.Range("A2").Formula = "=COUNTIF(" & Rng.Address & ",A5)>1"
Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:A2"), Unique:=False
Set Rng = Nothing
Exit Sub
If Err.Number <> 0 Then
msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg1$, , "FindDuplicates error", Err.HelpFile, Err.HelpContext
End If
GoTo Cleanup1
End Sub

You could have another button to unhide all the rows afterwards.

Sub ShowAllRows()
'Unhides rows hidden by advanced filter.
End Sub

Hope this is helpful to you.

Answer #3    Answered By: Ludo Ricci     Answered On: Aug 28

If Cells(h8) = Cells(h7) Then

is asking if there is a problem. I.e. you don't want the else.

Didn't find what you were looking for? Find more on Help on some code - like an "if" statement Or get search suggestion and latest updates.