CODEWITHSUNDEEP
vbaexcel
desmond.carros
desmond.carros

Reputation: 362

VBA - How to Replace Non-Zero Values with Column Header?

I was just tweaking with a data in VBA and was stuck at one problem. I was trying to impute a zero value with a "" and a non zero value with a column header.

My data looks like :

Customer Pasta Sandwich Pizza Burger X 01 00 01 00 Y 01 01 00 01 Z 00 01 00 01 A 00 00 01 01 B 01 01 01 01

Kindly note the data is in TSV format and it has numerous columns. Basically the data is variable and one can expect different number of columns in different data. I want to build my Macro so flexible that it can be executed on any similar data.

As of now i was able to achieve the code as below :

Sub clearzero()

Dim rng As Range

For Each rng In Range("B2:XFD1048576") 'Also i have used XFD1048576 as the end of workbook ( which i know is not feasible, if anyone can suggest how to select the data dynamically then i would be glad as well )

If rng.Value = "00" Then
    rng.Value = ""
If rng.Value = "01" Then
    rng.Value =??

Next End Sub

I am not able to figure out ?? in my code. My output would be like the below:

FinalOutPutRequired Can anyone suggest on the same?

Thanks

Upvotes: 1

Views: 440

Answers (2)

Vityata
Vityata

Reputation: 43585

Sub TestMe()

    Dim myCell As Range
    Dim wks As Worksheet        
    Set wks = Worksheets(1)

    Application.EnableAnimations = False
    For Each myCell In wks.UsedRange
        If myCell = "00" Then
            myCell = ""
        ElseIf myCell = "01" Then
            myCell = wks.Cells(1, myCell.Column)
        End If
    Next myCell

    Application.EnableAnimations = True

End Sub
  • wks.UsedRange refers to the used range of the first worksheet
  • myCell = wks.Cells(1, myCell.Column) refers to the cell in the frist row of the referred cell.
  • Application.EnableAnimations is used to speed up a bit the process.

Upvotes: 2

AJD
AJD

Reputation: 2438

A non-VBA solution.

In column E, place the formula =IF(B2>0,B$1,"") (Note the $ before the 1). You can then fill this formula across and down to cover the number of columns and rows in your original or expected data.

In terms of finding the end of your range in VBA use SpecialCells(xlCellTypeLastCell) (thanks to https://www.excelcampus.com/vba/find-last-row-column-cell/):

Sub Range_SpecialCells_Method()
    MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address
End Sub

Upvotes: 1

Related Questions