VBA Code numbering Cross Reference

[khikha1]

dear all

please need your help with a VBA code to find cross references and number them. i add a colors to explain my need. the code should take an item from column A and look in column B and C if it finds a reference for it it has to search column A again , if it find it or find them ,the code is to number them is column D. ( 1,2, ......) , if there is no Cross reference the cell in column D is to be Empty .
hope the attached picture is clear .
i really appreciate your help with this code as i am in real need for it .

regards
ziad alsayed .

 

[plettieri]

Hello khikha1

You might try this:

Sub NumberCrossReferences()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Dim reference As String
Dim counter As Long
Dim found As Boolean

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

' Find the last row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each item in column A
For i = 1 To lastRow
reference = ws.Cells(i, 1).Value
counter = 0
found = False

' Check if the reference exists in columns B or C
For j = 1 To lastRow
If ws.Cells(j, 2).Value = reference Or ws.Cells(j, 3).Value = reference Then
found = True
Exit For
End If
Next j

' If a reference is found, number the cross-references in column D
If found Then
For j = 1 To lastRow
If ws.Cells(j, 1).Value = reference Then
counter = counter + 1
ws.Cells(j, 4).Value = counter
End If
Next j
End If
Next i
End Sub

Some help trying to explain :​

1. Setting the Worksheet:
   • The code sets the worksheet to "Sheet1". Change "Sheet1" to the name of your sheet if it's different.
2. Finding the Last Row:
   • The code finds the last row in column A to determine the range of data to process.
3. Looping Through Each Item in Column A:
   • The code loops through each item in column A and initializes the reference, counter, and found variables.
4. Checking for References in Columns B and C:
   • For each item in column A, the code checks if the reference exists in columns B or C. If a reference is found, it sets found to True and exits the inner loop.
5. Numbering the Cross-References in Column D:
   • If a reference is found, the code loops through column A again and numbers the cross-references in column D.
6. Empty Cells in Column D:
   • If no cross-reference is found, the cell in column D remains empty.

​

hope this helps
plettieri

 

[StephenCrump]

Based on your description, a simple formula would suffice (which you could populate with a few lines of VBA code).

D2: =SUM(COUNTIF(A$2:A$9,B2:C2))

(which assumes that an item in column A can not reference itself in B or C)

But why do you have numbers in D4, D7 and D9 when you say:

if there is no Cross reference the cell in column D is to be Empty

And given that A5 is identical to A9, why are their results in column D different?

 

[khikha1]

dea

Hello khikha1

You might try this:

Sub NumberCrossReferences()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Dim reference As String
Dim counter As Long
Dim found As Boolean

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

' Find the last row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each item in column A
For i = 1 To lastRow
reference = ws.Cells(i, 1).Value
counter = 0
found = False

' Check if the reference exists in columns B or C
For j = 1 To lastRow
If ws.Cells(j, 2).Value = reference Or ws.Cells(j, 3).Value = reference Then
found = True
Exit For
End If
Next j

' If a reference is found, number the cross-references in column D
If found Then
For j = 1 To lastRow
If ws.Cells(j, 1).Value = reference Then
counter = counter + 1
ws.Cells(j, 4).Value = counter
End If
Next j
End If
Next i
End Sub

Some help trying to explain :​

1. Setting the Worksheet:
   • The code sets the worksheet to "Sheet1". Change "Sheet1" to the name of your sheet if it's different.
2. Finding the Last Row:
   • The code finds the last row in column A to determine the range of data to process.
3. Looping Through Each Item in Column A:
   • The code loops through each item in column A and initializes the reference, counter, and found variables.
4. Checking for References in Columns B and C:
   • For each item in column A, the code checks if the reference exists in columns B or C. If a reference is found, it sets found to True and exits the inner loop.
5. Numbering the Cross-References in Column D:
   • If a reference is found, the code loops through column A again and numbers the cross-references in column D.
6. Empty Cells in Column D:
   • If no cross-reference is found, the cell in column D remains empty.

​

hope this helps
plettieri

dear plettieri

thank for quick response , the code is numbering them "1" in Column D and not sequencing them (1,2, 3 ......) , will you adjust it please
thanks in advance .

regards
Ziad alsayed

 

[khikha1]

Based on your description, a simple formula would suffice (which you could populate with a few lines of VBA code).

D2: =SUM(COUNTIF(A$2:A$9,B2:C2))

(which assumes that an item in column A can not reference itself in B or C)

But why do you have numbers in D4, D7 and D9 when you say:

And given that A5 is identical to A9, why are their results in column D different?

good day,

numbers in D4, D7 and D9 are for example purpose only . this is how the code should number them. ( 1,2,3.........)
A5 and A9 are identical , not a problem .
regards
Ziad alsayed.

 

[StephenCrump]

the code is numbering them "1" in Column D and not sequencing them (1,2, 3 ......)

numbers in D4, D7 and D9 are for example purpose only . this is how the code should number them. ( 1,2,3.........)
A5 and A9 are identical , not a problem .

I think you are saying that the screenshot you posted doesn't show the correct results in Column D?

If so, can you please post some sample data, plus the numbers you expect to see in column D?

 

[khikha1]

I think you are saying that the screenshot you posted doesn't show the correct results in Column D?

If so, can you please post some sample data, plus the numbers you expect to see in column D?

dear stephen
your right , my apologies , please find attached what is expected in column D.

 

[StephenCrump]

please find attached what is expected in column D.

Thanks. A little different to what was first posted.

Can you please also explain the numbers in column D. It's not obvious to me what counts as 1, 2 or 3?

 

[khikha1]

Thanks. A little different to what was first posted.

Can you please also explain the numbers in column D. It's not obvious to me what counts as 1, 2 or 3?

my data is item numbers for stock, same articles are entered in different items number that is the reason i put the colors to make the expmale clearer, i am trying to number them in Clomn D to unify them .

 

[khikha1]

dea

dear plettieri

thank for quick response , the code is numbering them "1" in Column D and not sequencing them (1,2, 3 ......) , will you adjust it please
thanks in advance .

regards
Ziad alsayed

dear plettieri

please need your assistance