Vlookup link to pivot - text/number question
 

Hi,

Im linking my summary sheet to a pivot table using product codes. Because
the users have entered codes incorrectly, i keep getting n/a's where there is
obviously data in the pivot but isnt getting pulled through. This error
seems to disappear if i put an apostrophe at the start and if i retype the
whole code into the cell.

Is there a quick way of going through the errored ones and putting in the
apostrophe/retyping the code and ensuring this works?

Thanks!

Vlookup link to pivot - text/number question
 

Owl,

Select all the product code cells in your source data, and then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub NumberToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeConstants, 1)
myCell.Value = "'" & myCell.Value
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub



"Owl" wrote in message
...
Hi,

Im linking my summary sheet to a pivot table using product codes. Because
the users have entered codes incorrectly, i keep getting n/a's where there is
obviously data in the pivot but isnt getting pulled through. This error
seems to disappear if i put an apostrophe at the start and if i retype the
whole code into the cell.

Is there a quick way of going through the errored ones and putting in the
apostrophe/retyping the code and ensuring this works?

Thanks!

Vlookup link to pivot - text/number question
 

Hi Bernie

Im not experienced at all using macros - is there another way to fix this
problem?

"Bernie Deitrick" wrote:

Owl,

Select all the product code cells in your source data, and then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub NumberToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeConstants, 1)
myCell.Value = "'" & myCell.Value
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub



"Owl" wrote in message
...
Hi,

Im linking my summary sheet to a pivot table using product codes. Because
the users have entered codes incorrectly, i keep getting n/a's where there is
obviously data in the pivot but isnt getting pulled through. This error
seems to disappear if i put an apostrophe at the start and if i retype the
whole code into the cell.

Is there a quick way of going through the errored ones and putting in the
apostrophe/retyping the code and ensuring this works?

Thanks!

Vlookup link to pivot - text/number question
 

Try selecting all the cells with numbers and then formatting them for text.

HTH,
Bernie
MS Excel MVP


"Owl" wrote in message
...
Hi Bernie

Im not experienced at all using macros - is there another way to fix this
problem?

"Bernie Deitrick" wrote:

Owl,

Select all the product code cells in your source data, and then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub NumberToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeConstants, 1)
myCell.Value = "'" & myCell.Value
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub



"Owl" wrote in message
...
Hi,

Im linking my summary sheet to a pivot table using product codes. Because
the users have entered codes incorrectly, i keep getting n/a's where there is
obviously data in the pivot but isnt getting pulled through. This error
seems to disappear if i put an apostrophe at the start and if i retype the
whole code into the cell.

Is there a quick way of going through the errored ones and putting in the
apostrophe/retyping the code and ensuring this works?

Thanks!