ByRef Argument Mismatch while Calling a Function

[Valryn]

Hello all,

I'm new to mrexcel and new to VBA so I really hope this isn't a waste of everyone's time. I apologize if this has been asked elsewhere and I haven't been able to understand it correctly.

I have two functions that both work correctly on their own, but I would like to call one of them in a loop. This may not be the best way to set this up but I'm kinda looking for a quick and dirty solution at the moment.

So here's what I have:

Sub formatAll()

    Dim lastRow As Long
    
    lastRow = findLastRow(1)
    
    For n = 1 To lastRow Step 6
    
        Call formatCompany(n)
        
    Next n

End Sub

When I try to run it I get a compile error: "ByRef argument type mismatch"

The error originates from trying to call formatCompany() with "n".

Any help you could offer would be greatly appreciated.

Thanks, Valryn

 

[gsbelbin]

This is because you haven't declared n. If you don't declare it, it is created as a variant. You need to look at the function formatCompany and see what the parameter is declared as and then declare n as the same data type.

So you'll have something like

Function formatCompany(byref x as integer) as Integer

Then you need to put Dim n as Integer in the formatAll procedure

 

[Valryn]

Ah, thank you so much for the quick response. Yes, after declaring the loop variable as the correct data type for formatCompany() it worked perfectly. I wasn't aware that the loop variable was made as a variant, I just figured it magically worked itself out somehow. Haha. Anyway, thanks again!

Valryn