Auto Fill Cells

[pwill32]

Hi can anyone help?

I have attatched an Excel file to show examples

I would like F5:L7 to generate and auto fill with letters, depending on what letters are inputed into F2:L2.

I have put a reference guide on the left side in red A3:C29
to help show how things are calculated with the examples on the right N3:AS43

The values in brackets are a reference to cells, ie 'Z5' is the ref for 'Z6' the letter C and so on...

It looks more complicated than it actually is? this is the best way I can think to show what I am trying to achieve.

any help would be appreciated

many thanks

[mdmackillop]

Use a user defined function (UDF).. In F5 enter =X(F$2,$E5); copy across and down.

Function X(A, B)
X = Chr(Asc(A) + B)
End Function

[Bob Phillips]

A simple formula does it


=INDEX($A$4:$A$29,MATCH(INDEX($C$4:$C$29,MATCH(F$2,$A$4:$A$29,0))+$E5,$C$4: $C$29,0))

[pwill32]

A simple formula does it


=INDEX($A$4:$A$29,MATCH(INDEX($C$4:$C$29,MATCH(F$2,$A$4:$A$29,0))+$E5,$C$4: $C$29,0))

Thanks xld, I will give this a try

[pwill32]

Use a user defined function (UDF).. In F5 enter =X(F$2,$E5); copy across and down.

Function X(A, B)
X = Chr(Asc(A) + B)
End Function



Thanks I will give this a try

[pwill32]

Hi can anyone help?

I have numbers in A1:G102 and want to change the numbers to Letters using the reference in columns I1:K102,

I have give examples of the first three rows but would like a formula if possible to be able to drag down to the last rows to auto fill the rest,

I have uploaded an excel example,

any help would be apprieciated.

many thanks.

[Logit]

Im not certain if it can be done with only a formula. i would imagine if the answer is YES, the formula will be a humdinger long one.
(Saw another post - not certain if it was yours - where a short formula was used. So I was wrong. The formula doesn't have to be a long one.)

Here is a solution involving a FUNCTION and a small formula:

The formula would be: =ConvertToLetter(A1) for the cell A1.

The FUNCTION (pasted to a routine module) is :



Option Explicit
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function


In your scenario I pasted the formula in U1 ... dragged over to AA1, then down as far as necessary.

[Paul_Hossler]

I think it'd be easier to use an array-entered user defined function


19390




Option Explicit
Function Num2Let(R As Range) As Variant
Dim r1 As Range
Dim v() As String
Dim i As Long

Set r1 = R.Rows(1)
ReDim v(1 To r1.Columns.Count)

For i = 1 To r1.Columns.Count
v(i) = Chr(64 + r1.Cells(1, i).Value)
Next i

Num2Let = v
End Function




This doesn't use the 'key' table since it just adds 64 to the number value, but it could be extended to a not-nice key table

[Bob Phillips]

Im not certain if it can be done with only a formula. i w
ould imagine if the answer is YES, the formula will be a humdinger long one.

It looks a trivial one to me


=INDEX($K$1:$K$26,MATCH(A1,$I$1:$I$26,0))

This is similar, but simpler, to the one I offered in the OPs previous thread (http://www.vbaexpress.com/forum/showthread.php?59659-Auto-Fi).

[snb]

More trivial:

In M1:


=CHAR(64+A1)

in VBA


Sub M_snb()
[M1:S102] = [index(char(64+A1:G102),)]
End Sub

[mdmackillop]

As xld says, a simple formula, but going by my original thoughts
=CHAR(CODE(F$2)+$E5)

[Bob Phillips]

As xld says, a simple formula, but going by my original thoughts
=CHAR(CODE(F$2)+$E5)

My reason for not taking this approach Malcolm, even though it is simpler, is that it depends upon the lookup values being sequential :)

[mdmackillop]

@xld

it depends upon the lookup values being sequential
I should probably know better than to assume the question asked is the "correct" one. I live in hope.