CODEWITHSUNDEEP
excelvba
Danny
Danny

Reputation: 69

Format number to be able to search properly

I am trying to format some numbers where some have a leading zero so that I can then search them.

I am needing to format a set of numbers where all are 6 digits and some have a leading zero. I then have a separate code search those numbers for a specific one so the resulting format needs to be searchable. The first code below is the formatting I can't figure out and then the search code. If I simply do an "000000" for formatting I don't believe it works for my search anymore as those now become Special format. Help please?

Sub (First Code)

Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("O2:P" & lngLastRow).Select 'specify the range which suits your purpose
With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
End With

Dim SUPLCD As Range
Set SUPLCD = Range("Q2:Q")
With Selection
Selection.NumberFormat = "@"
Selection.Value = Format(Selection, "000000")
End With

End Sub


Sub Worksheet()

 Dim i As Long
    Dim j As Long

    Dim wsCurrent As Worksheet
    Set wsCurrent = ActiveSheet

    Dim wsData As Worksheet
    Dim rngData As Range
    Set wsData = ThisWorkbook.Worksheets("Tempinterior")

    Dim wsTempinterior As Worksheet
    ' Note that .Add will activate the new sheet so we'll
    ' need to reactivate the worksheet that was previously active
    Set wsTempinterior = Worksheets.Add
    wsTempinterior.Name = "copy"

    ' Find the used range in columns A to K and copy over starting
    ' at cell A1 of wsGalreq
    Set rngData = Intersect(wsData.UsedRange, wsData.Range("A:M"))

    ' Copy over the first row containing column headers
    j = 1
    rngData.Rows(1).Copy Destination:=wsTempinterior.Cells(j, 1)

    For i = 2 To rngData.Rows.Count
        ' Check cell of column 10 of row i and copy if matched
        If rngData.Cells(i, 10).Value = "026572" Or rngData.Cells(i, 10).Value = "435740" Or rngData.Cells(i, 10).Value = "622639" Then
            ' Copy over to wsDalreq from row j
            j = j + 1
            rngData.Rows(i).Copy Destination:=wsTempinterior.Cells(j, 1)
        End If
    Next

End Sub

With above code, the search doesn't pull the entries with those numbers I think because they are formatted as Special.

Upvotes: 0

Views: 99

Answers (2)

GMalc
GMalc

Reputation: 2628

You don't have to format Col Q to add a 0, you can accomplish your task with out formatting by using Like in your If statement. Because you are not clear about where the values are, you are formatting Col Q but searching Col J, I used Col Q.

Dim wsData As Worksheet
Set wsData = ThisWorkbook.Worksheets("Sheet1") '("Tempinterior")

Dim rngData As Range
Set rngData = Intersect(wsData.UsedRange, wsData.Range("A:M"))

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "copy"

    j = 1
    rngData.Rows(1).Copy Destination:=Sheets("copy").Cells(j, 1) 'copy headers for rngData

    For i = 2 To rngData.Rows.Count
        If wsData.Cells(i, 17).Value Like "26572" Or Sheet1.Cells(i, 17).Value = "435740" Or _
        Sheet1.Cells(i, 17).Value = "622639" Then
            j = j + 1
            rngData.Rows(i).Copy Destination:=Sheets("Copy").Cells(j, 1)
        End If
    Next i

End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152535

First avoid .Select and you will need to loop the change:

Sub first()

    Dim lngLastRow As Long

    With Worksheets("Sheet1") 'Change to your sheet

        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        With .Range("O2:P" & lngLastRow) 'specify the range which suits your purpose
            .NumberFormat = "General"
            .Value = .Value
        End With

        Dim SUPLCD As Range
        Set SUPLCD = .Range("Q2:Q" & lngLastRow)

        Dim rng As Range
        For Each rng In SUPLCD
            rng.NumberFormat = "@"
            rng.Value = Format(rng.Value, "000000")
        Next rng

    End With

End Sub

Upvotes: 0

Related Questions