0

I am experimenting with something:

There is a list with names, and what I would like to do, is to read the cell values in an array (this part works) than run a check for every cell in the worksheet and if a given cell is the same as a string inside an array, do something.

But unfortunatly I get the "type mismatch" error.

Ps. I know this doesn't make much sense and I could to that something inside the server function, but belive me I have my reasons. :-)

Edit: fixed a few things, now it looks like this (now I get the object doesn't support this property of method)

If it helps, you can also try it. You just need to add a cell with the name "Servers" and under it write some random words. Right now it should write in msgbox "ok" x times, where x is the number of rows you wrote in, under the cell, named "Servers"

1

'server name
Function server(ByVal issrvname As String)
Dim j As Integer
Dim c As Range
Dim x As Integer, y As Integer

For Each c In Sheets("Topology").UsedRange.Cells

Dim srvname() As String
j = 0
    If c.Cells.Value = "Servers" Then
    y = c.Column: x = c.Row + 1
        Do Until IsEmpty(Cells(x, y))
        ReDim Preserve srvname(0 To j) As String
        srvname(j) = Cells(x, y).Value
        x = x + 1
        j = j + 1
        Loop
    End If
Next c

For Each c In Sheets("Topology").UsedRange.Cells
    If IsInArray(c.Cell.Value, srvname) Then
        issrvname = True
    Else
        issrvname = False
  End If
Next c

End Function

2

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

3

Sub test()


Dim c As Range

For Each c In Sheets("Topology").UsedRange.Cells

    If server(c) = True Then
   MsgBox "ok"
    End If

Next c

End Sub
Improve this question
3
  • please highlight the rows where the error shows up Commented Jul 17, 2014 at 1:37
  • it does now show up, when I run the sub test() an msgbox appears saying: run-time error 13: Type mismatch Commented Jul 17, 2014 at 2:03
  • After the "Do", add Debug.Print x, y. After the For Each cell, add Debug.Print cell.address. This will narrow it down. Then you can Stop at the problem area and single-cycle (F8). See cpearson.com/excel/DebuggingVBA.aspx Commented Jul 17, 2014 at 2:12

2 Answers 2

Reset to default
2

I think you can condense your functions:

First you need to include your Array generating block to your main sub.
Including it in the Function server is slowing code execution because it needs to generate the array in every call of the server Function

Edit1: This is tried in tested now. I've re-written your function and improve your sub a bit.

Sub test()
    Dim j As Integer
    Dim c As Range, c1 As Range
    Dim x As Integer, y As Integer
    Dim i As Long '~~> added it just to check how many is shown in MsgBox

    For Each c In Sheets("Topology").UsedRange.Cells
        '~~> generate array if "Servers" is encountered
        If c.Value = "Servers" Then
            Dim srvname() As String
            j = 0
            y = c.Column: x = c.Row + 1
            With Sheets("Topology").UsedRange
                Do Until IsEmpty(.Cells(x, y))
                    ReDim Preserve srvname(j)
                    srvname(j) = .Cells(x, y).Value
                    x = x + 1
                    j = j + 1
                Loop
            End With
            '~~> use the generated Array of values here
            i = 1
            For Each c1 In Sheets("Topology").UsedRange.Cells
                If IsInArray(c1.Value, srvname) Then
                    MsgBox "ok" & i
                    i = i + 1
                End If
            Next c1
        End If
    Next c
End Sub

Here's the new function: (actually, you don't need it, you can call the Match function directly in main Sub)

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

Maybe you do this just for testing? I just thought that the sheet you use to generate the array must be different from the sheet you want to compare the server names.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

you are a genius, there are some other problems with the script as well. I try to get it working asap
Maybe you do this just for testing? I just thought that the sheet you use to generate the array must be different from the sheet you want to compare the server names. you are right, I want to use this on other worksheets as well. but if this part works, the rest will be easy. :-)
@Divin3 You already used it in a Function so same can be done in a Sub. Say you have a Sub named Test2, you can also pass it ByRef. You create the sub like this: Sub Test2(arr As Variant) then make a call on that sub in your main sub.
there is a problem that I dont understand. It fills the array with the values of the next column
I realized that i, has to be 0 instead of 1, because 0 is the first element of the array, but I dont understand, how does it gets to the next row, and why I have to write y = c.Column -1 to get it working right
2

I think it might be that you define c as a range in Test, but call server with c when server is expecting a boolean.

Improve this answer

3 Comments

@Divin3 I think above answer by RowanC nails it. Changing Function server(ByVal issrvname As Boolean) to this Function server(issrvname) will do the trick. But of course, you can be explicit with the ByVal/ByRef as well as the type. Like Function server(ByVal issrvname As String).
It would help OP (and make him/her happy) if you could provide a fix to your identified issue/problem. :)
this helped, but now I get object doesen't support this property of method

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.