PDA

View Full Version : Macro from Number to Letter



sooty8
08-24-2009, 08:10 AM
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.


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


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

Many thanks

Sooty 8

mdmackillop
08-24-2009, 08:16 AM
I'm not clear what should happen. Can you post your workbook?

mdmackillop
08-24-2009, 08:22 AM
Guessing you want to change 1 to 8 to letters

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

sooty8
08-24-2009, 09:04 AM
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


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


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
08-24-2009, 11:24 AM
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
08-24-2009, 11:39 AM
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
08-24-2009, 01:04 PM
Okay, we'll look out for it.