dj44
06-08-2017, 07:41 AM
folks,
good day can any one help me cut this down into more elgance looking its getting a bit long now i am getting confused with the long code
it basically searches the column and numbers the placeholder in each column -I am just repeating for each column
Sub Number_Items()
Dim lngIndex As Long: lngIndex = 1
Dim lngIndex1 As Long: lngIndex = 1
Dim oLastRow As Long, i As Long
'------ Column D : Category1
oLastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("D" & i)
.Value = Replace(.Value, "DXX", lngIndex) ' << Search and Replace here
lngIndex = lngIndex + 1
End With
Next i
'------ Column E : Category2
oLastRow = Range("E" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("E" & i)
.Value = Replace(.Value, "EXX", lngIndex) ' << Search and Replace here
lngIndex = lngIndex + 1
End With
Next i
'-------- Column F : Name
oLastRow = Range("F" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("F" & i)
.Value = Replace(.Value, "FXX", lngIndex1) ' << Search and Replace here
lngIndex1 = lngIndex1 + 1
End With
Next i
'-------- Column G : ID
oLastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("G" & i)
.Value = Replace(.Value, "GXX", lngIndex1) ' << Search and Replace here
lngIndex1 = lngIndex1 + 1
End With
Next i
'-------- Column H : Notes
oLastRow = Range("HXX" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("H" & i)
.Value = Replace(.Value, "&", lngIndex1) ' << Search and Replace here
lngIndex1 = lngIndex1 + 1
End With
Next i
End Sub
may be a select case statement but i couldnt work out where to put the code and the rest of the code for the case range
thank you for any help
good day can any one help me cut this down into more elgance looking its getting a bit long now i am getting confused with the long code
it basically searches the column and numbers the placeholder in each column -I am just repeating for each column
Sub Number_Items()
Dim lngIndex As Long: lngIndex = 1
Dim lngIndex1 As Long: lngIndex = 1
Dim oLastRow As Long, i As Long
'------ Column D : Category1
oLastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("D" & i)
.Value = Replace(.Value, "DXX", lngIndex) ' << Search and Replace here
lngIndex = lngIndex + 1
End With
Next i
'------ Column E : Category2
oLastRow = Range("E" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("E" & i)
.Value = Replace(.Value, "EXX", lngIndex) ' << Search and Replace here
lngIndex = lngIndex + 1
End With
Next i
'-------- Column F : Name
oLastRow = Range("F" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("F" & i)
.Value = Replace(.Value, "FXX", lngIndex1) ' << Search and Replace here
lngIndex1 = lngIndex1 + 1
End With
Next i
'-------- Column G : ID
oLastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("G" & i)
.Value = Replace(.Value, "GXX", lngIndex1) ' << Search and Replace here
lngIndex1 = lngIndex1 + 1
End With
Next i
'-------- Column H : Notes
oLastRow = Range("HXX" & Rows.Count).End(xlUp).Row
For i = 9 To oLastRow
With Range("H" & i)
.Value = Replace(.Value, "&", lngIndex1) ' << Search and Replace here
lngIndex1 = lngIndex1 + 1
End With
Next i
End Sub
may be a select case statement but i couldnt work out where to put the code and the rest of the code for the case range
thank you for any help