Final bit of VBA code help needed

[markster]

Hi guys, I just final bit of Macro code to sort this 65k line spreadsheet. I’ll try to explain it clearly

Column A has Ticket Number from A7 to A65k

Column B has a Category from K7 to K65k (typically Priority 1, Priority 2, Priority 3, etc.

Unfortunately, sometimes if a transaction is related to another ticket number it just puts the related ticket number in the category field rather than the category (it’s an issue with the system). The Category should be Priority 1, Priority 2, Priority 3 etc,

So as an example

Cell A7 contains Ticket Number 10101585 Cell K7 contains ‘Priority 1’

Cell A8 contains Ticket Number 10101586 Cell K8 contains ‘Priority 2’

Cell A9 contains Ticket Number 10101587 Cell K9 contains ‘10101585’ (which is the ticket number of the related call)

Cell A10 contains Ticket Number 10101588 Cell K10 contains ‘Priority 3’

Cell A11 contains Ticket Number 10101589 Cell K11 contains ‘’10101588 (which is the ticket number of the related call)

So every cell in column K should have a priority number Priority 1, Priority 2, Priority 3 etc. but sometimes (many times) it contains just a ticket number (as illustrated in K9 and K11 above). \

So using the example when the macro encounters a ticket number I need it to:

1. Move the ticket number from cell K9 to cell 09 (so I keep the cross reference)
2. Find the matching ticket number in Column A (in the example above it is in A7)
3. Enter the original ticket number priority in Cell K9 (in the example above it is Priority 1)

Sorry if this is a long winded explanation but I’ve written this about 10 times so it’s as clear as possible.

Please let me know if you have any questions.

Thanks in advance for you help

Mark

 

[NdNoviceHlp]

Hi Mark U cant trial this. Please back up your file before trying this. HTH. Dave

Option Explicit
Sub TestMark()
Dim Lastrow As Integer, Cnt As Integer, Rng As Range, C As Range
On Error GoTo ErFix
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheets("Sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set Rng = .Range(.Cells(7, 1), .Cells(Lastrow, 1))
End With
For Cnt = 7 To Lastrow
If IsNumeric(Sheets("Sheet1").Range("K" & Cnt).Value) Then
Set C = Rng.Find(Sheets("Sheet1").Range("K" & Cnt).Value, MatchCase:=True)
If Not C Is Nothing Then
Sheets("Sheet1").Range("O" & Cnt).Value = Sheets("Sheet1").Range("K" & Cnt).Value
Sheets("Sheet1").Range("K" & Cnt).Value = Sheets("Sheet1").Range("K" & C.Row).Value
End If
End If
Next Cnt
ErFix:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
If Err.Number <> 0 Then
MsgBox "Error"
End If
End Sub

ps. adjust sheet name to suit

 

[markster]

Works perfectly mate - thanks very much. I can sleep now!
Thanks again.
Mark

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Be safe. Dave