Hi Peter,
Let's get things from the beginning as the code became very complicated (for me at least) and it breaks the file instead or making it better.
First of all, please find the excel file on mediafire in the below link. There are two sheets. The first (databefore) is how I receive data from the online source and the second (dataafter) is the desired format I want to get after applying the code.
I didn't want to explain all these from the beginning that is why I tried to simplify the process with a shorter description than the actual, but let me explain in detail now as it will obviously make things easier.
https://www.mediafire.com/file/fzj4enlprthiyzj/Data.xlsx/file
Let me explain what every columns has to be/do:
A,B,C,D,E,G,AK = (have to remain untouched. Column B has to be in Date format and Column D has to be in Time format)
F = Text format (has to remain as it is and erase the "-" where it exists)
H,J,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ = Numerical data (Have to replace the "." with ",", erase the "-" where it exists and erase also the negative values. They have also to be transformed to numerical data instead of text)
I,K = Text values (Have to make all letters in Upper Case and leave everything as it is)
AL,AM = (This is the most tricky part that we have already solved. The data in brackets is the score of the first half, and the data below in the otherwise empty line is the score of the second half of the games. So, the data of the AL2 when the rest of the line is empty, have to go to the AM1 field and the empty data has to be deleted. Of course we have also to remove the brackets from the AL column. When the AL column is blank the line has to remain as it is as it is a game that has not yet been completed)
The first line has of course always to remain untouched as it contains the headings.
----------------------------------------------------------------------------------------------------------------------
The initial code below, solves the issue in the AL & AM columns but since it does the replacements in all columns (red code) it needs to be corrected to avoid changes in the I & K columns.
Sub MoveNameDeleteRow_v5()
Dim r As Long
Application.ScreenUpdating = False
For r = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 2 Step -1
If Cells(r, "AL").Value Like "[[]*]" Then
Rows(r).Delete
ElseIf IsEmpty(Cells(r, "AK").Value) Then
Cells(r - 1, "AM").Value = Cells(r, "AL").Value
Rows(r).Delete
End If
Next r
With Range("AL2", Range("AL" & Rows.Count).End(xlUp))
.Replace What:=")", Replacement:="", LookAt:=xlPart
.TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2))
End With
With ActiveSheet.UsedRange
.Replace What:=".", Replacement:=",", LookAt:=xlPart
.Replace What:="-", Replacement:="*", LookAt:=xlWhole
End With
Application.ScreenUpdating = True
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------
I tried to make the below corrections, but they didn't seem to work. If I delete the code in red, It does work, but the numerical data are still in text format that has to be changed...
Sub MoveNameDeleteRow_v5()
Dim r As Long
Application.ScreenUpdating = False
For r = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 2 Step -1
If Cells(r, "AL").Value Like "[[]*]" Then
Rows(r).Delete
ElseIf IsEmpty(Cells(r, "AK").Value) Then
Cells(r - 1, "AM").Value = Cells(r, "AL").Value
Rows(r).Delete
End If
Next r
'Removes the () symbols
With Range("AL2", Range("AL" & Rows.Count).End(xlUp))
.Replace What:=")", Replacement:="", LookAt:=xlPart
.TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2))
End With
'Changes the "-" with ""
With Intersect(ActiveSheet.UsedRange, Range("F:F, H:H, J:J, L:AJ"))
.Replace What:=".", Replacement:=",", LookAt:=xlPart
.Replace What:="-", Replacement:="", LookAt:=xlWhole
.NumberFormat = "General"
.Value = .Value
End With
'In column I ensure upper case
With Range("I2", Range("I" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(#="""","""",upper(#))", "#", .Address))
End With
'In column K ensure upper case
With Range("K2", Range("K" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(#="""","""",upper(#))", "#", .Address))
End With
Application.ScreenUpdating = True
End Sub
-------------------------------------------------------------------------------------------------------------------------
Please let me know if everything is clear and I will try also to make some amendments by myself in case I make it...
