Solved: My spreadsheet needs to be violated, I mean validated :)

[Sir Newbie]

Well hello there,

Firstly, let me give a big thanks to all the people out there that bother to reply to questions from people like me. Sure, there are some people who don't appreciate the time, patience and effort. But there are also some who do! So, as a person who has copied and pasted more code than they probably should have (although I have been known to try and read the help file from time to time)... Thank you.

Secondly, I have a problem. My doctor says the lump on the back of my neck... No, no, wait...

I would like to validate some data in a column using only VBA code.

I would also like to get next week's lottery numbers, but that's another story.

I need the data to be whole numbers between 0 and (let's say) 100. But when I use the following code, I get an error saying...

Run-time error '1004':
Application-defined or object-defined error


With Range("c6").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

I've tried the example code that comes with Excel 2000. I've tried code from other programmers. I've tried using code that involves the "If" statement. I've tried... Need I go on?

Oh the pain!

Overall, here's my objective...

I need to create a spreadsheet ("Spreadsheet C") that contains only the products that the user has ordered (so that they can print it out).

To achieve this...

"Spreadsheet B" gets its data from "Spreadsheet A" when the user (she's a good looking chick I just happen to know) clicks on a button that's located in "Spreadsheet B". Then "Spreadsheet B" identifies any errors, asks the user to correct them and when they are correct, it creates another spreadsheet ("Spreadsheet C").

In other words...

Spreadsheet A >>> Spreadsheet B = Spreadsheet C.

But it ain't happening.

Can anyone work this problem out?

I know I can't :)

Thanks in advance!

PS: This may surprise you, but I'm not a professional programmer. So, an answer in plain English would be greatly appreciated! Thanks :)

[OBP]

what is wrong with plain old
range("c6").select
if activecell <5 or activecell > 10 then msgbox "Please enter a value between 5 and 10"
if activecell - int(activecell) <> 0 then msgbox "value must be whole number"

it works for me.

[Sir Newbie]

G'day OBP,

Thanks for the reply, I appreciate it :)

I'm currently tucking into a post-midnight snack (sausage rolls and Doritos).

Eating and typing, typing and eating, I'll get back to you as soon as I've finished the food and the code you so graciously provided.

Cheers!

[Sir Newbie]

Okay. The results are in.

After devouring the food, I tried the following code...


Range("c6").Select
If ActiveCell < 5 Or ActiveCell > 10 Then
MsgBox "Please enter a value between 5 and 10"
ElseIf ActiveCell - Int(ActiveCell) <> 0 Then
MsgBox "value must be whole number"
End If


If the number "54" (for example) is in cell "c6" everything seems fine when I try and validate it (i.e. click on my button). A message box appears saying "Please enter a value between 5 and 10"

The weird thing is when I type "qwe" (for example) in cell "c6" I get the same message box (i.e. "Please enter a value between 5 and 10").

But this is text and not a whole number.

Hmmm.

No wait!

I think I just learnt what the following code does (thanks to the help file - see I told you I did read them... Occasionally.)


ElseIf ActiveCell - Int(ActiveCell) <> 0 Then


I believe the above code rounds the numbers to a whole number :)

(Okay, okay... Slow down now. Sit down at the computer. Don't wake the neighbours. This is not the time to lose it just because you're excited :))

Hmmm.

Oh... Oh...

Okay...I think we've got it!!!!


For obp = 6 To 15
Range("c" & obp).Select
If ActiveCell < 5 Or ActiveCell > 10 Then
MsgBox "Please enter a value between 5 and 10"
Exit Sub
ElseIf ActiveCell - Int(ActiveCell) <> 0 Then
MsgBox "value must be whole number"
Exit Sub
End If
Next obp


This (to the best of my knowledge) .... WORKS!!!

OBP, you are a legend!

You are now part of my program... Literally :)

Thanks once again :) :) :)

[Sir Newbie]

Can an administrator mark this as solved? I'm not too sure how to do it.

(Do I click on "Thread Tools" and then "Unsubscribe from This Thread"?)

Thanks!

[lucas]

There is usually a "mark thread solved" link under thread tools but due to a forum upgrade its not working yet. Will mark it solved for you. Glad you got your answer and I would like to encourage you to Cheer up a tad...:grinhalo:

[Sir Newbie]

Thanks lucas.

And don't you worry... I'm very happy as a result of the help I got :)

Cheers!