"For Each Cell In Range(Cells(etc....)" wont work in userform...(?)

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
301
This runs fine in module or worksheet I have...

Code:
Dim lCol As Long
Dim wt As Worksheet
Set wt = Worksheets("Employee Training Matrix")
lCol = ActiveSheet.UsedRange.Columns.Count

With wt
For Each Cell In Range(Cells(4, 7), Cells(4, lCol))

If Cell = "b" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next

End With

but excel doesnt like it one bit when its in a userform... Its probably the structure of how I need to define the range, but I cannot figure it out. :confused:

I get the error: "Compile error: Variable not defined" with "Cell" highlighted in the VBA window.

Please and thank you for any help/suggestions. :)

Keith
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,920
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You need a range variable. Don't use the keyword "Cell" here. Also, you use "With wt" but then don't reference wt the way you should (with dots). Use this:

Code:
Dim rCell As Range

With wt
    For Each rCell In .Range(.Cells(4, 7), .Range(4, lCol))
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
301
Awesome, thnk you Jon. (i did reference 'wt' in my code... just forgot to include it in my post. :) )

THanks again

You need a range variable. Don't use the keyword "Cell" here. Also, you use "With wt" but then don't reference wt the way you should (with dots). Use this:

Code:
Dim rCell As Range

With wt
    For Each rCell In .Range(.Cells(4, 7), .Range(4, lCol))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,628
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top