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

 

[MARK858]

Are you sure that your cell contains the real number 1 and not a number stored as text of 1

All you need to check the column number is to put the formula

=COLUMN(FV1)

in a cell

 

[hsandeep]

Are you sure that your cell contains the real number 1 and not a number stored as text of 1

Yes. The cells contains the real number 1 or 0 and not a number stored as text

 

[MARK858]

I set up the sheet as below

Book1
	FV	FW	FX	FY
10	1	0	0	1
l1

and stepped through the code below

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
           
  MsgBox .Cells(10, col).Address

            End If
        Next col

    End With

    Application.ScreenUpdating = True

End Sub

and the code entered the loop and gave the message box at the correct cell address when stepping through the code

 

[hsandeep]

Sir, what errors are in my code. 1 more: you have used Sub instead of Private Sub..should I also use Sub

 

[MARK858]

I can't see any error in the code, I think it is something wrong with the cells.
Change the FV10 in the formulas below to one of the cells with a 1 in it.
What do the formulas return?

=LEN(FV10)

=ISNUMBER(FV10)

I removed the Private for my testing, it makes no difference to the running of your code

 

[hsandeep]

I can't see any error in the code, I think it is something wrong with the cells.
Change the FV10 in the formulas below to one of the cells with a 1 in it.
What do the formulas return?

=LEN(FV10)

=ISNUMBER(FV10)

I removed the Private for my testing, it makes no difference to the running of your code

I will report you back with these. Plus, should I use Clear Formats in FV10:GZ10?

 

[MARK858]

should I use Clear Formats in FV10:GZ10?

Formats make no difference to the underlying value, having said that if you are using a format to hide decimal values then that would be your issue

 

[hsandeep]

I will report you back with these. Plus, should I use Clear Formats in FV10:GZ10?

=LEN(FV10)=1 & =ISNUMBER(FV10) returns TRUE.
I think, the code is not forced to check all cells in FV10:GZ10 fro 1. It is probably only checking FV10 & if FV10<>1 it jumps out.

 

[MARK858]

only checking FV10 & if FV10<>1 it jumps out.

Doesn't for me with

Book1
	FV	FW	FX	FY
10	0	0	0	1
l1