PDA

View Full Version : Sub reference as Cell



frubeng
04-27-2009, 05:03 AM
Hello,

I have a sub such as:


Sub is_stuff_happening(rng As range)
For Each C In rng
Call set_word(C)
Next C
End Sub

Sub set_word(C As ***)


And I don't know what should be put in the place of the ***. Range doesnt work and there doesnt seem to be a cell reference. Does anyone know?
Thanks in advance!!

Bob Phillips
04-27-2009, 05:12 AM
It should be Range.

frubeng
04-27-2009, 05:14 AM
It should be Range.

When I do that though, it highlights the C in set_word and gives me the error:
ByRef argument type mismatch

What to do?

mdmackillop
04-27-2009, 05:33 AM
Use Option Explicit


Sub is_stuff_happening(rng As range)
Dim C as range
For Each C In rng
Call set_word(C)
Next C
End Sub

Sub set_word(C As Range)

Paul_Hossler
04-27-2009, 05:40 AM
Might have to add .Cells to the For Each loop





Sub is_stuff_happening(rng As range)
Dim C As range
For Each C In rng.Cells
Call set_word(C)
Next C
End Sub


Sub set_word(C As Range)




Paul

frubeng
04-27-2009, 05:54 AM
Thanks for all the repsonses!

So i have this



Sub is_stuff_happening(rng As range)
Dim C As range
For Each C In rng.Cells
If IsNumeric(C.Value) And IsNumeric(C.Offset(0, -1).Value) Then
Call set_word(C)
End If
Next C
End Sub


Sub set_word(C As Range)



This now gives me another error, hilighting the If statement:

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

:dunno

Bob Phillips
04-27-2009, 06:04 AM
You will get an error if you try and access any cells in column A because of the Offset(0, -1)

frubeng
04-27-2009, 06:06 AM
Nice, i had the wrong cell input
Thank you so much everyone!!!


You will get an error if you try and access any cells in column A because of the Offset(0, -1)