Please correct the code

[hsandeep]

I am using a code in a Worksheet.
The code:

Private Sub FreezeValues()
    
    Dim sheetName As String, targetRange As Range, targetCell As Range
    sheetName = "l1" ' The name of your sheet

    Application.ScreenUpdating = False

    With ThisWorkbook.Sheets(sheetName)

        Dim col As Integer
        For col = 178 To 208 ' Column numbers for FV to GZ
            If .Cells(10, col).Value = 1 Then
            
                Set targetRange = .Range(.Cells(14, col).Address & ":" & .Cells(28, col).Address)
            
                For Each targetCell In targetRange
                    If Not targetCell.HasFormula Then
                        Application.EnableEvents = False
                        targetCell.Value = targetCell.Value2
                        Application.EnableEvents = True
                    End If
                Next targetCell
        
                Set targetCell = .Cells(65, col)

                If Not targetCell.HasFormula Then
                    Application.EnableEvents = False
                    targetCell.Value = targetCell.Value2
                    Application.EnableEvents = True
                End If
            End If
        Next col

    End With

    Application.ScreenUpdating = True

End Sub

The code line

If .Cells(10, col).Value = 1 Then

Should check if the value in the 10th row and col column of the current worksheet (as defined by the With statement) is equal to 1.
If the condition is true, then the code inside the If block should get executed. If the condition is false, then the code inside the If block should be skipped.
But the code is not entering the code lines after the If statement & jumps to

End If

Next targetCell

PLEASE RECTIFY THE CODE. Also, please check whether the code line is correct or not for column FV:GZ

For col = 178 To 208 ' Column numbers for FV to GZ

Thanks

 

[hsandeep]

Doesn't for me with

Book1
	FV	FW	FX	FY
10	0	0	0	1
l1

FV10:GZ10 contains formulas which generates either 0 or 1 like in FV10=

=IFERROR(IF(AND(FV4<$FK$4, FV4>$FL$4, FV4>$FM$4), 1, 0),0)

 

[MARK858]

I ran the code below

Private Sub FreezeValues()
    
    Dim sheetName As String, targetRange As Range, targetCell As Range
    sheetName = "l1" ' The name of your sheet

    Application.ScreenUpdating = False

    With ThisWorkbook.Sheets(sheetName)

        Dim col As Integer
        For col = 178 To 198 ' Column numbers for FV to GZ
        Debug.Print col
            If .Cells(10, col).Value = 1 Then
            Debug.Print " entered the loop " & .Cells(10, col).Address
            
                Set targetRange = .Range(.Cells(14, col).Address & ":" & .Cells(28, col).Address)
            
                For Each targetCell In targetRange
                    If Not targetCell.HasFormula Then
                        Application.EnableEvents = False
                        targetCell.Value = targetCell.Value2
                        Application.EnableEvents = True
                    End If
                Next targetCell
        
                Set targetCell = .Cells(65, col)

                If Not targetCell.HasFormula Then
                    Application.EnableEvents = False
                    targetCell.Value = targetCell.Value2
                    Application.EnableEvents = True
                End If
            End If
        Next col

    End With

    Application.ScreenUpdating = True

End Sub

with the data

Book1
	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP
4	8	4	4									3	5	3	8	6	9	8	1	5	8	10	7	5	5	4	6	9	7	6	8	7
5
6
7										1
8
9
10												0	1	0	0	1	0	0	0	1	0	0	1	1	1	0	1	0	1	1	0	1
l1

Cell Formulas
Range	Formula
FV10:GP10	FV10	=IFERROR(IF(AND(FV4<$FK$4, FV4>$FL$4, FV4>$FM$4), 1, 0),0)

and got the result

 178 
 179 
 entered the loop $FW$10
 180 
 181 
 182 
 entered the loop $FZ$10
 183 
 184 
 185 
 186 
 entered the loop $GD$10
 187 
 188 
 189 
 entered the loop $GG$10
 190 
 entered the loop $GH$10
 191 
 entered the loop $GI$10
 192 
 193 
 entered the loop $GK$10
 194 
 195 
 entered the loop $GM$10
 196 
 entered the loop $GN$10
 197 
 198 
 entered the loop $GP$10

So the code is definitely entering the loop

 

[MARK858]

If it is going to this line

Next targetCell

it is not the column number that is the issue, it is the block beginning

                For Each targetCell In targetRange
                    If Not targetCell.HasFormula Then

 

[hsandeep]

So the code is definitely entering the loop

There was mistake in the code (the way I had interpreted the 2 code lines). I changed the code as below, which now works.

Private Sub FreezeValues()
    
    Dim sheetName As String, targetRange As Range, targetCell As Range
    sheetName = "l1" ' The name of your sheet

    Application.ScreenUpdating = False

    With ThisWorkbook.Sheets(sheetName)

        Dim col As Integer
        For col = 178 To 208 ' Column numbers for FV to GZ
            If .Cells(10, col).Value2 = 1 Then
            
                Set targetRange = .Range(.Cells(14, col).Address & ":" & .Cells(28, col).Address)
            
                For Each targetCell In targetRange
                    If targetCell.HasFormula Then
                        Application.EnableEvents = False
                        targetCell.Value = targetCell.Value2
                        Application.EnableEvents = True
                    End If
                Next targetCell
        
                Set targetCell = .Cells(65, col)

                If targetCell.HasFormula Then
                    Application.EnableEvents = False
                    targetCell.Value = targetCell.Value2
                    Application.EnableEvents = True
                End If
            End If
        Next col

    End With

    Application.ScreenUpdating = True

End Sub

 

[MARK858]

Happy that you have it sorted

 

[hsandeep]

Happy that you have it sorted

Yes. I am happy...got some 'push' from your efforts rather hefty efforts. Thanks a lot MARK858. May SHYAM BABA bless you with HIS blessings

 

[MARK858]

You're welcome