Final bit of VBA code help needed

markster

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,957
Hi Mark U cant trial this. Please back up your file before trying this. HTH. Dave
Code:
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

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Works perfectly mate - thanks very much. I can sleep now!
Thanks again.
Mark
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,259
Members
425,460
Latest member
Astros1243

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
Top