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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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,474
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,690
I cannot thank you enough. You help me so many times! Thank you !!!
 

Joe4

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

Forum statistics

Threads
1,081,935
Messages
5,362,243
Members
400,672
Latest member
ExcelGrasshopper

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top