algorith to generate A1 style text row/col values Excel VBA

December 14, 2011 at 08:27:37
Specs: Windows XP & Vista, 2Ghz/ 500Mb
Some operations - particularly setting print area seem to require only text A1 style ranges.
So I wrote this routine
******************************
Function lets(k)
If k < 27 Then
lets = Chr(64 + k)
Else
a2 = k \ 26
a1 = k Mod 26:
If a1 = 0 Then a1 = 26: a2 = a2 - 1
lets = Chr(64 + a2) & Chr(64 + a1)
End If
End Function

and used it thus
printrange = lets(k) & "1:" & lets(k + 6) & 25
ActiveSheet.PageSetup.PrintArea = printrange

k is obviously variable in practice and the rows are not
And it works!

Hope this solves someones problem.


See More: algorith to generate A1 style text row/col values Excel VBA

Report •

#1
December 14, 2011 at 11:09:35
Nice, but you could have used the Split function and saved a lot of coding...

Function lets(k)
 lets = Split(Columns(k).Address(0, 0), ":")(0)
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 16, 2011 at 09:26:02
Thanx

How do you find a function to do what you want if you don't know it exists? Address is a function and I look at properties by default. It is quicker. Elegance is not the answer unless you are looping big time.

When I started programming (Fortran would you believe?) I found that with manuals (remember them?) you had to read and digest every page before you could possibly use any one page. Plus ca Change!


Report •

#3
December 16, 2011 at 09:53:26
re: "How do you find a function to do what you want if you don't know it exists?"

You could Google the task you're trying to perform, such as:

VBA return column letter from column number.

Reading other people questions and the various answers provided can open up a whole new world of functions as well as new ways to use the functions that you are already familiar with.

I do it all the time and I use/modify the information I find to help provide solutions in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 18, 2012 at 08:26:02
Google?
I programme from home and install at work.

Not everyone has or can have broadband. Some people run 5 websites on dial-up and use other peoples' bandwidth.

Google / Yahoo yes. But the answer depends on how you phrase the question. The best answers come when you already have the answer.

Google has its limits. And I don't particularly want to buy an algorithm. Thankyou.

I will go and pester Google.

I may be some time...................


Report •

#5
January 18, 2012 at 10:29:46
Was there a point there someplace? If so, I missed it.

re: "The best answers come when you already have the answer."

You're kidding, right?

I didn't know that the SPLIT function existed until I Googled for ways to convert a column number from a column letter after reading your original post.

All it took was one try, a little reading (during which I learned a few other techniques that will come in handy) and I found the SPLIT function.

Why re-invent the wheel by writing UDF's when functions already exist to accomplish your goal?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 20, 2012 at 09:34:49
No I am not kidding. I learned that lesson years ago.

We made a rail weighbridge and were asked to add a refinement and they said
"wagons -fitted or unfitted" we asked what fitted meant they said "with or without brakes" - we decided that brakes would not affect the result. Would it?

Only when watching the thing go wrong and noting a wagon not listed did we ask what it was.

They said "a brake van" we said we were not told about it. The said "without brakes it has to have a brake wagon"

the point is (obvious innit?)
You dun gotta know enough of the answer to ask the right question.


Report •

Ask Question