Best I can think of is to make my attempt dynamic
Code: Select all
Sub CallAddheader()
Call AddHeader(1, "B", "H", "Yasser .")
End Sub
Sub AddHeader(ByVal ofst As Long, ByVal Clm1 As String, ByVal Clm2 As String, ByVal Head As String)
Range("" & Clm1 & "4:" & Clm2 & "4").Value = Evaluate("=" & """" & Head & """" & " & " & "Column(" & Clm1 & ":" & Clm2 & ") - " & ofst & "")
End Sub
Sub CallAddheader2()
Call AddHeader2(1, "2", "8", "Yasser .")
End Sub
Sub AddHeader2(ByVal ofst As Long, ByVal Clm1 As Long, ByVal Clm2 As Long, ByVal Head As String)
Range("" & CL(Clm1) & "5:" & CL(Clm2) & "5").Value = Evaluate("=" & """" & Head & """" & " & " & "Column(" & CL(Clm1) & ":" & CL(Clm2) & ") - " & ofst & "")
End Sub
' Column letter http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Function CL(ByVal lclm As Long) As String 'Using chr function and Do while loop For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
Do
Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL
Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
End Function
Yasser 2.JPG
( Don't forget to copy the Function,
Function CL(ByVal lclm As Long) As String )
I can't think of anything more elegant.
Alan
_.______________________________________-
Edit. Just for completeness, if you don't want to use that extra function
Code: Select all
Sub CallAddheader3()
Call AddHeader3(1, "2", "8", "Yasser .")
End Sub
Sub AddHeader3(ByVal ofst As Long, ByVal Clm1 As Long, ByVal Clm2 As Long, ByVal Head As String)
Range("" & Split(Cells(1, Clm1).Address, "$")(1) & "6:" & Split(Cells(1, Clm2).Address, "$")(1) & "6").Value = Evaluate("=" & """" & Head & """" & " & " & "Column(" & Split(Cells(1, Clm1).Address, "$")(1) & ":" & Split(Cells(1, Clm2).Address, "$")(1) & ") - " & ofst & "")
End Sub
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also