A simple VBA "for each" loop with "call" function issue

[two0two1]

Hello Everyone.
Happy new year!
I am new to VBA and am facing issue with the below mentioned code. I am getting "Object Required" code error. Can someone please point me in the right direction.
I have written similar codes earlier without the "call" function and they have worked.


Sub callwithdata()
Dim mycellx As Variant
For Each mycellx In [a1:a9]
If mycellx.Value > 10 Then
Call subwithdata(16, True, "Calibri")
End If
Next mycellx
End Sub

Private Sub subwithdata(fontsize As Integer, fontbold As Boolean, fontname As String)
With mycellx.Font
.Size = fontsize
.Bold = fontbold
.name = fontname
End With
End Sub


(If this post needs to be moved to any other sub-category, please let me know. Thanks.)

[rollis13]

Have a try with these two changes I applyed to your macro:
Sub callwithdata() Dim mycellx As Variant
For Each mycellx In [A1:A9]
If mycellx.Value > 10 Then
Call subwithdata(mycellx, 16, True, "Calibri") '<= changed
End If
Next mycellx
End Sub


Private Sub subwithdata(mycellx, fontsize As Integer, fontbold As Boolean, fontname As String) '<= changed
With mycellx.Font
.Size = fontsize
.Bold = fontbold
.Name = fontname
End With
End Sub

[Paul_Hossler]

Hello Everyone.
Happy new year!
I am new to VBA and am facing issue with the below mentioned code. I am getting "Object Required" code error. Can someone please point me in the right direction.
I have written similar codes earlier without the "call" function and they have worked.

Welcome to the forum - take a minute and read the FAQ in the link in my signature for the rules of the road

I added CODE tags to the macro in your post



Really a Scope issue, not a Call issue

mycellx is only visible within the Sub where it is Dim-ed, unless it's Dim-ed outside of all subs/functions and then it's visible to all within the module


Homework assignment:

https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility




Option Explicit


Sub CallWithData()
Dim mycellx As Range , Only visible within CallWithData

For Each mycellx In ActiveSheet.Range("a1:a9").Cells
If mycellx.Value > 10 Then
Call SubWithData(mycellx, 16, True, "Calibri") ' using Call()
SubWithData mycellx, 16, True, "Calibri" ' not using Call()
End If
Next mycellx
End Sub


'need to tell SubWithData what cell to apply changes to
Private Sub SubWithData(R As Range, fontsize As Long, fontbold As Boolean, fontname As String)
With R.Font
.Size = fontsize
.Bold = fontbold
.Name = fontname
End With
End Sub

[two0two1]

Thank you both. I knew it was some silly mistake from my end. The place from where I am learning the VBA didn't mention in any example that the range/variant chosen needs to be mentioned within the sub. I will check out the MS docs and some better resources. Thanks!