Cell Notation

[rekclaw]

Is there a way to change the cell notation that is generated by VB.

I'm creating some nested if formulas something along the lines of:

activecell.offset(0,-1).formula = "=if(" & activecell.address & "> 0," & activecell.offset(0,1).address & "," & activecell.offset(0,2).address & &quot"

Everything seems to work ok with the following being generated in cell a1
=if($B$1>0,$C$1,$D$1)
Once i've nested this a couple of times to come up with a really ugly "if" statement, it still seems to work fine. But when I start to move around columns, the links to the cells seems to be broken. If I rewrite my code so that it generates:
=if(B1>0,C1,D1)
the links seem to be maintained. Off course once I have rewritten my code to handle this, I end up with hard coded columns in my code:

activecell.offset(0,-1).formula = "=if(B" & activecell.row & "> 0,C" & activecell.row & ",D" & activecell.row & &quot"

I would like to keep the flexibility of using the address property, but get the dollar signs out of the address of a cell.


Sorry if some of the code doesn't work...haven't done a syntax check. Just writting off the top of my head.


Thanks

rekclaw

 

[SkipVought]

Hi,

I HATE A1 notation!!!

For VBA I SO MUCH MORE PREFER Cells(row, col) notation.

Are you inserting FORMULAE into sheets via VBA for some particular reason? You can do that just fine on a worksheet. Is this some code that the user will execute on demand or in some other way? If it is, why not just calculate everyting in your program and put the values on the sheet.

I may be all wet Skip,
metzgsk@voughtaircraft.com

 

[acron]

If you the cell notation generated whilst recording a macro, you can set it to relative by clicking on the Relative Reference button on th emacro toolbar (it is by default beside the Stop recording button).

AC

 

[acron]

If you mean the cell notation generated whilst recording a macro, you can set it to relative by clicking on the Relative Reference button on th emacro toolbar (it is by default beside the Stop recording button).

AC

 

[rekclaw]

I guess I need to provide a little more info.

I'm pulling 5 years worth of payroll data from a variety of databases for a couple hundred employees (one workbook for each employee). Once I get the data into the spreadsheet, it gets categorized a little bit into a second spreadsheet and summarized into a third (with links between all three). The whole reason this is being done in Excel and not Access is that the end user may need to change the data as it is getting summarized. Thus, there is too much data to have the user manually key the links, if statements, etc. I also cannot put straight values into cells because there is always the potential for a value to change thus affecting the totals on the summary page.

And then of course, no one wants to see negative values (which is where this horrific if clause comes into play).

I've gone through several iterations of changes on format and formulas. I have overcome this "opportunity" by using globally defined offsets for each column, but when I use the address property it gives me that goofy dollar sign problem. When the end user starts to insert columns for their own purposes, this dollar sign problem raises its ugly head.

Rekclaw

 

[ilses]

Address has a couple of optional arguments. Maybe these help?

MsgBox (ActiveCell.Address(rowabsolute:=False, columnabsolute:=False))

ilses

 

[SkipVought]

Sounds like a nightmare to me. You know that Excel has some analysis and summary functionality in the Data menu item -- SubTotals and Pivot Table are both powerful tools for analysis and sumarization.

Skip,
metzgsk@voughtaircraft.com