Help with Excel, figuring out column letters 1

[TinyNinja]

Hey everyone,

I am working with Excel and have hit a wall and hopefully someone can give me a hand. I have a list that determines the number of columns data needs to be entered into Excel. With each run, the user can pick the number of items they want to view so the Excel will expand and shrink depending on the user. Also, the user has the ability to add items to the list whenever they want so the code needs to be able to expand freely into never before used columns.

I'm using this code to help me throw the Excel Formulas into the row & column it belongs.

loxls.Cells(lRow, lCol).Value = [=A2 + B2]

The problem now is, I'm entering data row by row in the sheet and I need to change the formula up on most of the cells.
I can't put this into my loop

[=A2 + B2]

since it depends on what row & column the loop is at which is in a variable.
I'm trying to figure out if there is a way to convert the column number into the correct Excel column letter. So something like 5 = C, 20 = T, 30 = AD, 40 = AN and so on. I have the rows covered no problem but can't figure out a way to change the column to the correct letters.

I need something like this (which I know is totally incorrect)

loxls.Cells(lRow, lCol).Value = "=" + Cells(lRow, lCol) + " + " + Cells(lRow, lCol) + " / " + Cells(lRow, lCol)

that changes to this

loxls.Cells(lRow, lCol).Value = [=D33 + AA32 / AD33]

My current way would be something like this

loxls.Range([C] + lRow).Value = [=D] + lRow + [+AA] + Alltrim(Str(aa,5,0)) + [ / AD] + lRow

^^^^ I need the D & AA & AD to be changed from a LCol variable into those letters and not hardcoded.

Has anyone come across this problem? I'm thinking I might need to create a function to do it manually but hoping their is like a VFP command or VFP Function that can do this automatically that I don't know about. Any help will be greatly appreciated!

 

[Chris Miller]

Well, the major thing you miss is that you want to set a formula, not a value, and a formula is set into a property called FormulaR1C1

You think emulating what you manually do to distinguish a value from a formula, by starting with = works in eel automation, too. I don't think so, may be wrong, but I'm very sure FormulaR1C1 exists, no idea why they named it that way, there also is Formula, actually.

Anyway, to address a cell you then have to use letters, that's right. But you get this from another property: Cell(1,1).Address is "A1", for example.
So knowing which is your highest column number you can ask Excel for its Address by .Cells(row,highestcolum).Address and use that.


Chriss

 

[TinyNinja]

Thank you Chris!!

I was not able to get FormulaR1C1 to work but I did get Formula to work. This is the result, exactly what I wanted!

loxls.Cells(3, 8).Formula = [=(] + loxls.Cells(2,8).Address + [*0.015)*(1-0.03)]

result

=($H$2*0.015)*(1-0.03)

Updated to

loxls.Cells(3, 8).Formula = [=(] + loxls.Cells(2,8).Address(.f., .f.) + [*0.015)*(1-0.03)]

result

=(H2*0.015)*(1-0.03)

I removed the anchoring since I don't want the user to make a change which breaks the formulas due to it being locked on that 1 cell.

 

[Chris Miller]

Meanwhile, I remembered what's the difference between Formula and FormulaR1C1. The name actually indicates it and is a mnemonic once you know it, in FormulaR1C1 you address cells by RnCn, so R1C1 is Cells(1,1) or A1. So you don't have to lookup the Address and can work with row and column numbers.



Chriss

 

[SkipVought]

Hi,

Usually a table with a column having a formula, need only be entered in ONE cell ann then COPIED and then PASTED down through all the rows in that column of the table.

Your formula [t]=A2+B2[/t] can be entered INE TIME in row 2 and COPY/PASTEd into all rows of data in that column.

I your table happens to be a Structured Table, it will automatically propagate to each row of the table in the column.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[TinyNinja]

Hey Skip,

You just gave me a great idea. I was going through a loop to enter all the data but now I want to just fill all the formulas into Row 2 and Autofill down. I have hit a wall though and can't match up the VB code to VFP.

This is the recorded macro

Range("C3:G3").Select
Selection.AutoFill Destination:=Range("C3:G22"), Type:=xlFillDefault
Range("C3:G22").Select

I was thinking something like this but I'm getting errors. I have tried several different mixtures of this with no success.

loxls.Range([c3:g3]).AutoFill([c3:g20]).select

Any suggestions?

 

[TinyNinja]

I found this and I believe this is the solution I am looking for. It's doing the autofill down that I am looking for.

loxls.Range([a3:i30]).DataSeries(2 , 4, 1 , 1)

Unless someone found something different.

 

[SkipVought]

This might work...

Range("C3:G3").AutoFill Destination:=Range("C3:G22"), Type:=xlFillDefault

Best to avoid Select and Activate whenever possible.

Unless you have an Excel VBA object library, you'll need a constant for xlFillDefault (0)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[TinyNinja]

Hey Skip,

I want the line of code to be in VFP and I believe this fits what I am looking for

loxls.Range([C3:G30]).DataSeries(2 , 4, 1 , 1)

Everything is working the way I need it to. Thanks for the help

 

[SkipVought]

Wesaf,

Welcome to Tek-Tips.

This forum deals with FoxPro-related topics.

Could you explain how Greek mythology relates to Excel and column letters as reflected above?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[TamarGranor]

I'm pretty sure it's spam designed to make people click on that link. I reported it and every one else should, too.