code to call a function

[ninjalearner]

i have written this code to code a function nameexists. I tried running it without success. Pls can the pro's help me look into it and make suggestions for ammendment.


Dim address As Range, name As String


Dim resp As String 'temp variable to hold input box variable; pass contents to address and name

Dim i As Integer


Set address = Range("A2:A14")

For i = 2 To 14

name = Range("address").Cells(i).Value

resp = InputBox("Enter Name Here", "Greetings")

Next

MsgBox "Does Name Exist? " & NameExists(address, name)

End Sub


Function NameExists(address As Range, name As String) As Boolean


Dim resp As String

Dim cell As Range

Dim i As Integer


NameExists = False


For Each cell In Range("address").Cells.Value

If name = resp Then

NameExists = True


End If


Next


End Function

 

[Hui]

Ninjalearner


To assign a range to an array

first Dimension the array as a Variant

then just assign it

You don't need to step through the array cell by cell

like:

[pre]

Dim name As Variant
name = Range("A2:A14").Value
Now you have an array Name which has 12 values stored like

Name(12,1) 


ie:

Name(1,1)

Name(2,1)

.

.

.

Name(11,1)

Name(12,1)


To tidy up the whole code

I suggest you modify as below

Sub h()
Dim Name As Variant
Dim Resp As Variant

Name = Range("A12:A14").Value
Resp = InputBox("Enter Name Here", "Greetings")
MsgBox "Does Name Exist? " & NameExists(Name, Resp)
End Sub

Function NameExists(Name As Variant, Resp As Variant) As Boolean
NameExists = False
For Each c In Name
If c = Resp Then NameExists = True
Next
End Function

[/pre]

 

[Guest]

However,One of the busie, refrigerator and freezer.Back in the mid. Rinse it long enough till you no longer see soap suds flowing down. Good question. You can indeed find an that fits both your ration and ambition be authentic and highly adept Carpet cleaning amounts aspiration alter from company to company; although they tend to be no accessory than £2.
PSI and GPM ratings - an entity consumers need to understand is namely forever power washers are devised for alter jobs.

 

[bobhc]

Can some one please tell me why shoes1796 and others post this crap, just what do they expect to get from it other than really p*%*ing of the true users of this board

 

[Hui]

see comments below each line:


Sub h()

Dim Name As Variant

Dim Resp As Variant


Name = Range("A12:A14").Value

'Assigns the values from the Range to the Variant

'Producing a 12 Row x 1 Column Variant Array


Resp = InputBox("Enter Name Here", "Greetings")

MsgBox "Does Name Exist? " & NameExists(Name, Resp)

End Sub


Function NameExists(Name As Variant, Resp As Variant) As Boolean

'Pass Name and Resp to here as Variants


NameExists = False

'Set default NameExists value


For Each c In Name

'Loop through each element in the Name array


If c = Resp Then NameExists = True

'Check if the Resp = to the current element of the array

Next


End Function