VBA code to loop throug a column and find/replace

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
80
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Use below code

VBA 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.
 
Upvote 0
Use below code

VBA 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.
 
Upvote 0
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

VBA Code:
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.
 
Upvote 0
your Column M will be 13 and column N will be 14

The code will look like

VBA Code:
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.
 
Upvote 0
What is your column per week criteria?
can you share part of your worksheet via xl2bb??
 
Upvote 0
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.

VBA Code:
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
 
Last edited by a moderator:
Upvote 0
Solution
Great (y), 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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top