hello,
I'm new here and new in vba.
I just created the command button to have a new row with border and auto numbering in A9 and it work perfect.
I wish to have an auto uppercase in B9:I9 each time new row added.
I tried below code but it only work when new row added, the value will convert to uppercase in B10:I10.
I tried edit the Uppercase code to B8:I8 but the value still converted to uppercase in B10:I10.
Code
Private Sub CommandButton1_Click() Dim mySheets
Dim i As Long
mySheets = Array("Sheet1")
For i = LBound(mySheets) To UBound(mySheets)
With Sheets(mySheets(i))
.Range("A9").EntireRow.Insert Shift:=xlDown
.Range("A9:I9").Borders.Weight = xlThin
End With
Call AutoNum
Call Upper
Next i
End Sub
Sub AutoNum()
Dim LastRow As Long
Dim counter As Long
LastRow = Range("F" & Rows.Count).End(xlUp).Row
For counter = 9 To LastRow
Range("A" & counter).Value = counter - 8
Next counter
End Sub
Sub Upper()
With Range("B9:I9", Cells(Rows.Count, "B").End(xlUp))
.Value = Evaluate("INDEX(UPPER(" & .Address(External:=True) & "),)")
End With
End Sub
Display More
*If anyone willing to answer, with respect, please explain it in simple word and simple English because I am not too good. Thanks