How to use VBA to reference a column letter instead of number in worksheet function

[Excellor_From_London]

Hi,

I've set up dynamic ranges that feed into a formula inserted via VBA below. Most of this works fine but I'm struggling to how reference the last column in the formula.

The last column is determined dynamically via the following.

Missing = Range("B1").End(xlToRight).column

However it returns the column number, not the actual column letter, e.g. if the last column is column G, it returns 7 and instead of the letter. But I need the letter of the column for the function to work. Currently all the other parameters work fine, it is just missing part that is cause the code to fall over.

Is that a way to translate a column number into the equivalent column letter?

Hope someone can suggest something.

Thanks,

Fellow Excellor

Set Regout = Range("g3") 'cell to start populating values
Lastrow = Range("B1").End(xlToRight).End(xlDown).Value
startrange = Range("A11").Value
Missing = Range("B1").End(xlToRight).column

Regout.FormulaArray = "=linest(" & "B" & startrange & ":" & "B" & lastrow & "," & "c" & startrange & ":" & MISSING & lastrow & ",true,true)"

 

[doofusboy]

You could either Enum columns A through IV as 1 through 255 or you could get the cell address with Range("B1").End(xlToRight).Address and use Excel's Split function to extract the column letter.

For example:

If Range("B1").End(xlToRight).Address returns $G$1, then you could use Split(Range("B1").End(xlToRight).Address, "$")(1) to return the 'G'.

 

[Excellor_From_London]

How to return relative referance via the Address Function in a formula

Hi,

I'm trying to use the address function to insert a formula. But the following line only returns an absolute reference to the underlying cells, not a relative.

i.e U14 returns the following underlying formula: =$B$14-$S$14

Cells(startrange, "U").Formula = "=" & Cells(startrange, 2).Address & "-" & Cells(startrange, 19).Address

I want to return a relative referance instead: =B14-S14. How can I change the formula to accomodate this?

Thanks,

FE

 

[VoG]

Try

Cells(startrange, "U").Formula = "=" & Cells(startrange, 2).Address(False, False) & "-" & Cells(startrange, 19).Address(False, False)