Macro from Number to Letter

[sooty8]

Hi

I have used the macro recorder for the code below - I have the problem
that some of the cells in each row will be empty and if they are empty I don't know how to leave them empty any help much appreciated.

[vba]
Sub Macro3A()
'Macro converts a number(1) to a letter
Application.ScreenUpdating = False
Sheets("Breeder").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "A"
Selection.AutoFill Destination:=Range("I2:I24"), Type:=xlFillDefault
Range("I2:I24").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = "B"
Selection.AutoFill Destination:=Range("J2:J24"), Type:=xlFillDefault
Range("J2:J24").Select
Range("K2").Select
ActiveCell.FormulaR1C1 = "C"
Selection.AutoFill Destination:=Range("K2:K24"), Type:=xlFillDefault
Range("K2:K24").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "D"
Selection.AutoFill Destination:=Range("L2:L24"), Type:=xlFillDefault
Range("L2:L24").Select
Range("M2").Select
ActiveCell.FormulaR1C1 = "E"
Selection.AutoFill Destination:=Range("M2:M24"), Type:=xlFillDefault
Range("M2:M24").Select
Range("N2").Select
ActiveCell.FormulaR1C1 = "F"
Selection.AutoFill Destination:=Range("N2:N24"), Type:=xlFillDefault
Range("N2:N24").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "G"
Selection.AutoFill Destination:=Range("O2:O24"), Type:=xlFillDefault
Range("O2:O24").Select
Application.ScreenUpdating = True

End Sub
[/vba]

All you experts out there will I know an easier way of doing this.

Many thanks

Sooty 8

[mdmackillop]

I'm not clear what should happen. Can you post your workbook?

[mdmackillop]

Guessing you want to change 1 to 8 to letters
[VBA]
Sub Macro3A()
'Macro converts a number(1) to a letter
Dim cel As Range
Application.ScreenUpdating = False
With Sheets("Breeder")
For Each cel In .Range("I2:O24")
If cel <> "" Then cel = Chr(64 + cel)
Next
End With
End Sub

[/VBA]

[sooty8]

Hi
Thank you for your quick reply -- I can't send the workbook to much personal data on the sheets -- however I understand how your code works but it means entering the numbers 1 to 7 so that it works OK. What does happen is I use the following to enter on sheet1

[VBA]
Private Sub Add605_Click()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Dim iRow As Long
Dim ws As Worksheet
Dim LngPos As Long
Set ws = Worksheets("Pools")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
On Error Resume Next
ws.Cells(iRow, 1).Value = UserForm2.Tb825.Value
ws.Cells(iRow, 3).Value = UserForm2.Tb826.Value
ws.Cells(iRow, 4).Value = UserForm2.Tb2A * 0.5
ws.Cells(iRow, 5).Value = UserForm2.Tb3A * 1#
ws.Cells(iRow, 6).Value = UserForm2.Tb4A * 2#
ws.Cells(iRow, 7).Value = UserForm2.Tb5A * 3#
ws.Cells(iRow, 8).Value = UserForm2.Tb6A * 5#
ws.Cells(iRow, 9).Value = UserForm2.Tb7A * 1#
ws.Cells(iRow, 10).Value = UserForm2.Tb8A * 2#
End Sub
[/VBA]

In each textbox I enter the number 1 and that then enters the correct figure on the sheet1 in the correct cell -- I was then going to pastelink from sheet1 into the Breeder sheet and hopefully then convert them myself the trouble is there are over 200 rows to scroll through and I'm bound to make mistakes. You guys always seem to solve my problems and I was again going to the top experts.

Many Thanks

Sooty 8

[Bob Phillips]

I am still confused as to what you want to do.

Can you not knock up an example workbook, with a before and after position?

[sooty8]

Hi Xld

Thanks for the reply will have to be tomorrow before I can knock up a small workbook -- don't think you will enjoy my database attempts though.

regards

Sooty8

[Bob Phillips]

Okay, we'll look out for it.