PDA

View Full Version : Checking entries to see if it is a valid entry



lucpian
06-04-2008, 08:21 AM
Hi All,

I am not very well grounded in Excel VBA, and I do have an assignment to write a vba code that will check an entry to see if it's a valid entry, and if not it will give a message stating that it is not a valid entry. I wrote the following code, but it is not working. Please, can someone help me out.
Function ChecMyEntry(ColumnHeader As String, MyEntry As Long)
Dim rowcount As Long
Dim R As Long
Dim c As Range
Dim strVal As String
'This is when they reposition the headers
'but still at row 1
With ActiveSheet.Range("A1:IV1")
Set c = .Find(ColumnHeader, LookIn:=xlValues)
If Not c Is Nothing Then
'This is adapted to count the no of rows in active column
rowcount = ActiveSheet.Range(Split(c.Address, "$")(1) & "65536").End(xlUp).Row
For R = 2 To rowcount
strVal = LTrim(RTrim(ActiveSheet.Cells(R, c.Column).Value))
'MsgBox Len(strVal)
ActiveSheet.Cells(R, c.Column).Value = strVal
If strVal <> "" And (Not (strVal) = MyEntry)) Then
Msgbox "That is an invalid entry"
End If
Next R
Else
MsgBox "Header : - " & ColumnHeader & " - not found !", vbInformation
End If
End With
End Function

Sub MyEntryValidtion()
msgbox"Please, enter your entry"
End Sub



Thanks

Lucpian

xld
06-04-2008, 09:51 AM
Tell us in words what you are trying to do.

lucpian
06-04-2008, 12:05 PM
I want to write a vba code that will check entries against a column "countG" in an excel sheet, and if the entries are valid it accepts the value, else, it gives a message of invalid entry. That is all I am trying to achieve. Thanks

Lucpian:(

xld
06-04-2008, 01:13 PM
... check entries ... what does that mean?

... if the entrues are valid ... what does that mean?

lucpian
06-04-2008, 01:31 PM
The table contains entries or values that are valid inthe columnG, however, if a user enters something different from these valid entries, then a message should pop up saying invalid entries. So, I am required to come up with a VBA code to check entries in this column against the valid entries in the table. Hence, I need the help of the gurus in this forum.

Thanks

LUCPIAN:(

RonMcK
06-04-2008, 02:51 PM
lucpian,

Let me add to the confusion by asking a couple of questions.

So, the column headed 'CountG' contains the short list of acceptable (valid) values for entries? Is it sorted and have duplicates been removed? About how many 'items' are in 'CountG'?

Where on the target worksheet do you want to validate entries? A specific column (perhaps also named 'CountG'?)? Or all cells with in the used range (rows first to last used, columns first to last used)? Or some other range?

What do you want done with empty cells with in the range being tested?

What do you want done when an invalid entry is found?

This may help xld.

Thanks,

lucpian
06-05-2008, 10:04 AM
Let me add the last piece of code which is not working, possibly that will help to explain what I am trying to achieve. Simply put, if a user enters a Can Number, it has to be validated to see if it is valid or not. Here is a new code which is still not working.


Function ChecCanCode(ColumnHeader As String, MyCanCode As String)
Dim rowcount As Long
Dim R As Long
Dim c As Range
Dim strVal As String
Dim sCanCode As String
Dim iResponse As Integer
Dim sMssg As String
'This is when they reposition the headers
'but still at row 1
With ActiveSheet.Range("A1:IV1")
Set c = .Find(ColumnHeader, LookIn:=xlValues)
If Not c Is Nothing Then
'This is adapted to count the no of rows in active column
rowcount = ActiveSheet.Range(Split(c.Address, "$")(1) & "65536").End(xlUp).Row
For R = 2 To rowcount
strVal = LTrim(RTrim(ActiveSheet.Cells(R, c.Column).Value))
'MsgBox Len(strVal)
ActiveSheet.Cells(R, c.Column).Value = strVal
sCanCode = Trim(txtCanCode) & ""
If sCanCode <> "" Then
If IsNull(DLookup("[Can]", "[Can] = '" & CanCode & "'")) Then
sMssg = "The Can " & CanCode & " does not exist."
iResponse = MsgBox(sMssg, vbOKOnly + vbExclamation, "Can Not Found")
Else
g_sCanCode = sCanCode
'DoCmd.Close acForm, "frmFindPO"
'DoCmd.OpenForm "New Postings"
End If
End If
Next R
Else
MsgBox "Header : - " & ColumnHeader & " - not found !", vbInformation
End If
End With
End Function

Sub CanCodeValidtion()
ChecCanCode

End Sub



Thanks to all those who genuinely wants to help me with this problem.

Lucpian

Paul_Hossler
06-06-2008, 07:49 PM
Have you thought about using Excel's Data Validation? Don't need VBA, but Pasting wil wipe out the validation checks for those cells.

If not, another question: Seems that after the data is all entered, you run a CheckSub (???). Might be "cleaner" to use a Worksheet_SelectionChange event to trap bad entries right away.

Paul

mdmackillop
06-07-2008, 01:58 AM
What is not working when you step through the code?
Post a sample workbook with all the relevant code. Your function is using outside variables. Maybe they are in error.