How do I change a cell based on the value of another cell?

0

Problem: Importing data does not come with leading zeros

I imported data from a text file, however, even if I configured it to import in TEXT format, the cells of a column that were in the 08 file are only imported as 8. As I'm going to use the concatenate function, I need to have zero the left.

The best solution would be to fix this problem in the source, that is, to import the files as they were written.

However, if this is not possible, I noticed:

  • The problem only occurs when cells in the first column are "C170"
  • When I use the = text function (Y2; "00") fix the problem from zero to left, but I have to create a new column with the fix and it gets out of hand.

What I wanted was the most efficient way (preferably without using the clipboard or creating new columns!) to add those leading zeros to use the concatenate.

I tried to do with this code, but it changed records that did not meet the conditions:

 For Each cell In rng.Cells
            If cell.Value = "c170" Then
            ElseIf cell.Offset(0, 30) = "8" Then
            dd = cell.Offset(0, 30).Value
            With cell.Offset(0, 30)
            .NumberFormat = "@"
            .Value = "0" & dd
            End With
              End If
    
asked by anonymous 03.11.2014 / 18:11

1 answer

1

You can use the Format function of the following way:

Dim numeroQualquer As Integer
Dim numeroFormatado As String

numeroQualquer = 8
numeroFormatado = Format(numeroQualquer, "00")

The value of the variable numeroFormatado would be 08 .

  

Note: Change where%% is_to display more or less zeros to the left.

    
27.12.2014 / 20:49