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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
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


Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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:
Code:
    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

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
I cannot thank you enough. You help me so many times! Thank you !!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
You are welcome!
:)
 

Forum statistics

Threads
1,077,917
Messages
5,337,163
Members
399,129
Latest member
ericosman

Some videos you may like

This Week's Hot Topics

Top