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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
I cannot thank you enough. You help me so many times! Thank you !!!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top