VBA code to loop throug a column and find/replace

[Balajibenz]

Hello People,

Can someone help me with below requirement.

I have a cell address stored under address1. now I want a code which loops through entire column of "address1" and if a number zero is found then it has to check the value of its immediate left cell and

1. if it is NA then zero should be replaced by NA
2. if it is empty then zero should be removed the cell should be empty
3. if it has any other value then zero should be replaced by NA.

Thank you in advance.

 

[fadee2]

Use below code

lr = Sheets("sheet1").Range("a2").SpecialCells(xlCellTypeLastCell).Row
For I = 1 To lr
    If Cells(I, 6) = 0 And Cells(I, 5) = "na" Then
        Cells(I, 6) = "NA"
    Else
        If Cells(I, 6) = 0 And Cells(I, 5) = "" Then
            Cells(I, 6) = ""
        Else
            If Cells(I, 6) = 0 And Cells(I, 5) <> 0 Then
                Cells(I, 6) = "NA"
            End If
        End If
    End If
Next I

you can change below values,
lr = total rows to lookup and edit,
6 = column F,
5 = Column E,
best suited to your needs.

 

[Balajibenz]

Use below code

lr = Sheets("sheet1").Range("a2").SpecialCells(xlCellTypeLastCell).Row
For I = 1 To lr
    If Cells(I, 6) = 0 And Cells(I, 5) = "na" Then
        Cells(I, 6) = "NA"
    Else
        If Cells(I, 6) = 0 And Cells(I, 5) = "" Then
            Cells(I, 6) = ""
        Else
            If Cells(I, 6) = 0 And Cells(I, 5) <> 0 Then
                Cells(I, 6) = "NA"
            End If
        End If
    End If
Next I

you can change below values,
lr = total rows to lookup and edit,
6 = column F,
5 = Column E,
best suited to your needs.

Hi Mate,

Thank you so much. As I said I have got a cell address (for eg :N3) stored in address1. So above code should happen in column N while comparing it with Column M and this changes week by week. So can you help to me to amend it so that it automatically looks in the column related to Address1.

 

[fadee2]

Hi Mate,

Thank you so much. As I said I have got a cell address (for eg :N3) stored in address1. So above code should happen in column N while comparing it with Column M and this changes week by week. So can you help to me to amend it so that it automatically looks in the column related to Address1.

you can change below values,
lr = total rows to lookup and edit,
6 = column F,
5 = Column E,
best suited to your needs.

your Column M will be 13 and column N will be 14

The code will look like

lr = Sheets("sheet1").Range("a2").SpecialCells(xlCellTypeLastCell).Row
For I = 3 To lr
    If Cells(I, 14) = 0 And Cells(I, 13) = "NA" Then
        Cells(I, 14) = "NA"
    Else
        If Cells(I, 14) = 0 And Cells(I, 13) = "" Then
            Cells(I, 14) = ""
        Else
            If Cells(I, 14) = 0 And Cells(I, 13) <> 0 Then
                Cells(I, 14) = "NA"
            End If
        End If
    End If
Next I

Hope that helps.

 

[Balajibenz]

your Column M will be 13 and column N will be 14

The code will look like

lr = Sheets("sheet1").Range("a2").SpecialCells(xlCellTypeLastCell).Row
For I = 3 To lr
    If Cells(I, 14) = 0 And Cells(I, 13) = "NA" Then
        Cells(I, 14) = "NA"
    Else
        If Cells(I, 14) = 0 And Cells(I, 13) = "" Then
            Cells(I, 14) = ""
        Else
            If Cells(I, 14) = 0 And Cells(I, 13) <> 0 Then
                Cells(I, 14) = "NA"
            End If
        End If
    End If
Next I

Hope that helps.

Hi Mate,

The column in which the code has to look for 0 will change week on week. so from the above i have to manually change the column number every week.
I have a address1 in my code where a cell address is saved(eg: N3). this will change week on week hence if i have a code which extracts column number from address1 and uses it for the rest of the code then i dont have to go and amend it everytime. hope it is clear now.

 

[fadee2]

What is your column per week criteria?
can you share part of your worksheet via xl2bb??

 

[Balajibenz]

What is your column per week criteria?
can you share part of your worksheet via xl2bb??

No problem mate, i have figured out as below.

a = Range(Address1).Column

For i = 4 To Lr
    If Cells(i, a) = 0 And Cells(i, (a - 1)) = "NA" Then
        Cells(i, a) = "NA"
    Else
        If Cells(i, a) = 0 And Cells(i, (a - 1)) = "" Then
            Cells(i, a) = ""
        Else
            If Cells(i, a) = 0 And Cells(i, (a - 1)) <> 0 Then
                Cells(i, a) = "NA"
            End If
        End If
    End If
Next i

End With

 

[fadee2]

Great , glad you got it working as needed.

No problem mate, i have figured out as below.

a = Range(Address1).Column

For i = 4 To Lr
If Cells(i, a) = 0 And Cells(i, (a - 1)) = "NA" Then
Cells(i, a) = "NA"
Else
If Cells(i, a) = 0 And Cells(i, (a - 1)) = "" Then
Cells(i, a) = ""
Else
If Cells(i, a) = 0 And Cells(i, (a - 1)) <> 0 Then
Cells(i, a) = "NA"
End If
End If
End If
Next i

End With