Help with VBA code

[markster]

Good evening

I have a report of few thousand lines that I'm trying to sort in a better way.

Column E has Customer last name from E7 e.g. Smith

Column K contains reference number date from K7 - the reference number is in the following format: TH_G0_S10_H_13084129

I'm looking for some macro code that will do the following

1. Add the customer last name to the reference number is column K so the reference number will look like this: Smith_TH_G0_S10
2. Remove the H_ number from the reference number column and add to column O so column O will read H_13084129

Any help you can give would be much appreciated.

Thanks in advance.
Mark

 

[mumps]

The code I suggested won't work on a Mac because the Mac doesn't recognize "Scripting.Dictionary". Did you try Fluff's suggestion in Post #9?

 

[markster]

I'll try the new code

 

[markster]

Works perfectly mate thank you very much.
Mark

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[markster]

hey mate - sods law the reference number format has now changed. Instead of being: TH_G0_S10_H_13084129 it now has an additional bit TH_G0_S10_J0_H13084129 (see orange text so there is now an additional underscore then a letter and then a number). Can anyone help me amend the code that fluff kindly created for me which is:

Sub markster()
Dim Ary1 As Variant, Ary2 As Variant, Ary3 As Variant
Dim r As Long

r = Range("E" & Rows.Count).End(xlUp).Row
Ary1 = Range("E7:E" & r).Value2
Ary2 = Range("K7:K" & r).Value2
ReDim Ary3(1 To UBound(Ary1), 1 To 1)
For r = 1 To UBound(Ary2)
Ary3(r, 1) = Split(Ary2(r, 1), "_", 4)(3)
Ary2(r, 1) = Ary1(r, 1) & "_" & Replace(Ary2(r, 1), "_" & Ary3(r, 1), "")
Next r
Range("K7").Resize(r - 1).Value = Ary2
Range("O7").Resize(r - 1).Value = Ary3
End Sub

Thanks very much.
Mark

 

[Fluff]

Can anyone help me amend the code

In what way?

 

[markster]

Hi sorry I should have explained. So customer name is in Column E
Reference number is in Column K and also part of it is moved to Column O

Example

Customer Name in K7 Smith
Reference Number in column K7 is TH_G0_S10_H_13084129

Currently the Macro will add the customer name to Cell K7 and move the H_13084129 bit of the reference number to Cell 07

The end result is that Cell K7 will display Smith_TH_G0_S10 and Cell 07 will display H_13084129

So now an additional bit has been added to the reference number i.e TH_G0_S10_J0_H_13084129 the macro now splits the reference number as follows Cell K7 displays Smith_TH_G10_S10 Cell O7 displays J0_H_13084129

Cell K7 SHOULD display Smith_TH_G10_S10_J0 and Cell O7 should display H_13084129

Hope this explains it clearly.

Thanks again. Mark

 

[Fluff]

Ok, how about

Sub markster()
   Dim Ary1 As Variant, Ary2 As Variant, Ary3 As Variant
   Dim r As Long
   
   r = Range("E" & Rows.Count).End(xlUp).Row
   Ary1 = Range("E7:E" & r).Value2
   Ary2 = Range("K7:K" & r).Value2
   ReDim Ary3(1 To UBound(Ary1), 1 To 1)
   For r = 1 To UBound(Ary2)
      Ary3(r, 1) = Split(Ary2(r, 1), "_", 5)(4)
      Ary2(r, 1) = Ary1(r, 1) & "_" & Replace(Ary2(r, 1), "_" & Ary3(r, 1), "")
   Next r
   Range("K7").Resize(r - 1).Value = Ary2
   Range("O7").Resize(r - 1).Value = Ary3
End Sub

 

[markster]

Works perfectly mate thanks again - much appreciated.

Have a great evening.
Mark

 

[Fluff]

My pleasure.