Set Formula With Range ("E5:" & Lastusedcol & lastusedrow) Also Column letter from Col num

[mangesh.mehendale]

hiii,

What I want is below code... But GetColumnletter Not Working

Please Login or Register  to view this content.

[zbor]

GetColumnletter is custom function that you need also to copy in your workbook if you want it to work.
If it can't find it then you'll get error at this point.

[mangesh.mehendale]

Okay got it.... Please let me know the way to tackle this in above code I want to define "With Range("E5:" & celletr & Lastcol)" ...
let me know the code for celletr

[humdingaling]

here is what microsoft suggests

Please Login or Register  to view this content.

https://support.microsoft.com/en-gb/kb/833402

[xlnitwit]

Hi,

There is no necessity to convert the column number if you use the Cells property

Please Login or Register  to view this content.

[mangesh.mehendale]

hii Thanks for suggesting Cell Property but Somthing is wrong... it is pasting formulae till Col TL even though there is data till Col UR

[mangesh.mehendale]

but Working correctly after below modification...

Please Login or Register  to view this content.

[Oppressed1]

In case useful, very compact and (reportedly) CPU efficient column letter methods:

VBA, for range rng:
Mid(rng.Address,2,InStr(2,rng.Address,"$")-2)

Not asked, but for completeness, for regular Excel for cell C2:
=char(64+column(C2))

That is tempting because it's so very compact, but Excel master Aladin Akyurek much preferred the following, and at the time clocked it as fastest of several choices, so I now only use this for regular Excel:

=SUBSTITUTE(ADDRESS(1,COLUMN(C2),4),"1","")
for cell C2 on current sheet. Substitute {D5} for {C2} to get a different cell like D5

Substitute {anysheet!D5} for {C2} to get D5 from another sheet. (I know that seems silly on the surface if you think about it )

[Doc.AElstein]

Here allso a collection of some ways to get the Column Letter from a Column Number

http://www.excelforum.com/tips-and-t...explained.html