How to change a cell based on the value of another cell?

Asked

Viewed 3,804 times

0

Problem: Import data does not come with zeroes on the left

I imported data from a text file, but even configuring to import in TEXT format, the cells of a column that in the file was 08 are imported only as 8. As I will use the concatenate function, I need to have zero left.

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

But if not possible, I noticed:

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

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

I tried to do it with this code, but he altered records that don’t 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
vba

  • I’m not sure I understand the problem.. Maybe I said something stupid in the answer, you can tell me if that’s what you’re waiting for or if not, you could give me more details of why you’re not answering?

  • I’m sorry, Math, I’ve never been on the platform before. The formula does not solve, because I concatenate more than 37 cells, and add this formula in all is unfeasible.

  • Thanks for the clarification. I will try to answer appropriately.

  • You don’t just take the if second line? I don’t understand what he’s doing there.

  • It applies the condition only if the cell value is "C170"

  • really, as you said, the ideal would be to fix the problem in the import, however for that it is necessary that you show us how you are doing, I gave an answer using the function Format, see if it fits you

  • Please post your spreadsheet. I am feeling "in the dark" to help you better. If you have trouble providing company information, replace it with values that don’t match, but keep something to test if it works. at.

Show 2 more comments

1 answer

1

You can use the function Format as follows:

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 it is "00" to display more or less zeros on the left.

Browser other questions tagged vba

You are not signed in. Login or sign up in order to post.