CODEWITHSUNDEEP
excelvba
Jolly Sapper
Jolly Sapper

Reputation: 45

Find/Replace not finding cell value after formatting

There is a column of values that are moved from one Excel spreadsheet to another, in the same workbook, by a macro. The values should be five characters, only numbers, including leading zeros.

There are several ways that have successfully replaced the lost leading zeros from the auto formatting that Excel does. With a strange result.

For every cell that the macro has formatted the cells, the Find/Replace tool refuses to recognize any searches that include zeros.

Example:
Before Macro = 9093
After Macro = 09093

The Find/Replace window will find a search value of 9093 but will not find a search value of 09093. A Find/Replace window will find a positive hit after deleting the macro formatted 09093 and hand keying 09093 into the cell.

I have not tried code checking each value for the desired number of characters then concatenating leading zeros until the right number of characters has been reached. My hesitation stems from my assumption that a macro running this code will run very slow when having to go through 1000 or so rows.

Code blocks below are two attempts:

''Masks for a five character sequence.
' Corrects for leading zeros being dropped for Product Code column.
' Currently does not work.
Columns("F:F").Select
Selection.NumberFormat = "00000"

''Alternative method for keeping correct format of Product Code
' (with leading zeros) and searchable with Find window.
' Also not functioning.
Dim a
Dim l As Long

With Range("F2", "F" & lastUsedRow)
   .NumberFormat = "#"
   a = .Value
   For l = 1 To UBound(a, 1)
    a(l, 1) = Right("0000" & a(l, 1), 6)
   Next l
   .Value = a
End With

Upvotes: 2

Views: 150

Answers (1)

SilentRevolution
SilentRevolution

Reputation: 1513

The actual value of the cell you are trying to find is 9093, even though it is shown as 09093 through formatting. The find/replace tool will look for a string value of 09093 while the actual value is 9093 and thus cannot find it. Presumably when you key in the 09093 it is formatted as text rather than a number to preserve the leading 0s.

If you don't actually use the numbers in the newly created column for analysis, might I suggest the line below. This way you can find the cell with the leading 0's from the Find/Replace dialog as the entire product number including the leading 0's are a string.

Selection.NumberFormat = "@" 'This formats the selected cell as text

Upvotes: 1

Related Questions