VBA code to loop throug a column and find/replace

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Balajibenz

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

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
What is your column per week criteria?
can you share part of your worksheet via xl2bb??
 

Balajibenz

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

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,454
Messages
5,624,846
Members
416,062
Latest member
NIDHYAMARI

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
Top