This code determines if the argument is a number or an alphabet, returns the alphabet if it is a number, and returns the number if it is an alphabet.
- Function Replace_NA_or_AN(ByVal val As Variant) As Variant
-
Dim buf As String
-
If IsNumeric(val) = True Then
-
buf = Cells(1, val).Address(True, False)
-
Replace_NA_or_AN = Left(buf, InStr(buf, "$") - 1)
-
Else
-
Replace_NA_or_AN = Range(val & "1").Column
-
End If
-
End Function
Example
- Sub sample2()
-
Dim S As Variant
-
Dim i As Integer
-
For i = 1 To 40
-
S = Replace_NA_or_AN(i)
-
Cells(1, i).Value = S
-
Next i
-
For i = 1 To 40
-
S = Cells(1, i).Value
-
Cells(2, i).Value = Replace_NA_or_AN(S)
-
Next i
-
End Sub
