Code will not run because of how the data is formatted in a column.

[gheyman]

I have code that someone on this site graciously provided a long time ago. Below is what I required as is the code MickG provided. I am trying to use this code again, but It doesn’t like the format of the data in column A. If I type over whats in there, the code runs fine. My problem is the data is coming off a report. Its pasted in (There is a large number of rows, so typing over it one by one is not a solution).

Any idea how I change the code or a way to change the formatting in column A so this would run. I tried just highlighting the row and changing the format to numbers. I tried cutting and pasting values…

The code just will not run with the current format. I know its looking to numbers and the code is all based on numbers. N

Any suggestions?

***************************Previous Post just as a Reference*************
I need to display parent, in parent column, where there is a parent child relationship based on an indentured level listed in a column
Column A: Indentured Column B: Part Number Column C: Parent

Indenture Part Number Parent
1- ABC
2- 123
3- 123.1x
3- 12ab
2- 455
3- pqp
4- 1@233

I am looking for a formula (or code) that would fill in column C - "Parent"

The results would look like this;

Indenture- Part Number- Parent
1- ABC-
2- 123- ABC
3- 123.1x- 123
3- 12ab- 123
2- 455- ABC
3- pqp- 455
4- 1@233- pqp

Sub Find_Parent()
Dim Rng As Range, Dn As Range, n As Long, R As Range
Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .exists(Dn.Value) Then
    .Add Dn.Value, Dn
Else
    Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
End If
Next Dn
For Each Dn In Rng
    If .exists(Dn.Value - 1) Then
    If Not .Item(Dn.Value - 1) Is Nothing Then
        For Each R In .Item(Dn.Value - 1)
            If R.Row < Dn.Row Then
'Dn.Offset is Which column, relative to the column it searching, to place the result
                Dn.Offset(, -1).Value = R.Offset(, 1).Value
            End If
        Next R
    End If
 End If
 Next Dn
End With
End Sub

 

[Joe4]

Is the issue that there are numbers in column A that are coming over as text when copying/pasting?
If so, you should be able to easily convert the whole column at once to numbers by doing a simple Text to Columns.

If you turn on your Macro Recorder, you will get the VBA command to do this, which looks like this:

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

 

[gheyman]

I cannot thank you enough. You help me so many times! Thank you !!!

 

[Joe4]

You are welcome!