Start Free Trial

asked on

How To Write This in 2007 Syntax

Attached is a very generic version of a current project. Initially, the first five cells of each row in the UserCost Table in the Costs Worksheet were supplied by another method within the VBA program. However, with the need to have the two Tables synced, I manually inserted the appropriate formulas that would allow for the first five cells of the UserCost Table to be synced with the Log Table in the LogFile Worksheet.

Where the difficulty arises is writing the VBA code so that when a new row is inserted, those same formulas can be written into each cell. I was able to do this in the frmUnits Code, but it's not translating over.

The code is in Sheet3 ("LogFile") and starts "With Sheet4". Those lines of code in question are commented out, as errors kept arising. Would anyone know how to write this type of code?

Thanks
TableSyntax.xlsm

I haven't looked at the logic yet, but I did find syntax errors.

Shouldn't the With Sheet4 code be like this?

Dave

With Sheet4
                      .Activate          'Defined Names
                      .Rows([nSht3VisibleRow]).Hidden = False
                      .Rows([nSht3VisibleRow]).Interior.Color = 4210752
                       Application.EnableEvents = True
                      .Range(.Cells([nSht3NextRow], Range("Log[IDX]").Column), .Cells([nSht3NextRow], [nSht3LastCol])).Interior.Pattern = xlNone
                       Application.EnableEvents = False
                      .Cells([nSht3NextRow], Range("UserCost[IDX]").Column) = .Cells([nSht3LastRow], Range("Log[IDX]").Column) + 1
                       .Cells([nSht3NextRow], Range("UserCost[IDX]").Column).FormulaLocal = "= Log[[#This Row],[IDX]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Name]").Column)).FormulaLocal = "=Log[[#This Row],[LABs]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Project_ID]").Column)).FormulaLocal = "=Log[[#This Row],[Project_ID]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Date Out]").Column)).FormulaLocal = "=Log[[#This Row],[Date Out]]"
                      .Range(.Cells([nSht3NextRow], Range("UserCost[Year]").Column)).FormulaLocal = "=Log[[#This Row],[Year]]"
                     
           End With

Open in new window

ASKER

Dimille,

I guess I need to take a look at the workbook that I attached. What you have is exactly what my TableSyntax shows. Maybe, in switching between books, the code got upgraded in one book and not transferred over.

Part of the issue is the error that it doesn't recognize the UserCost Table. The VBE seemed to prefer the .Cells versus the .Range. It didn't have any initial compile errors, but still hiccuped when it came to the .Cells portion.

I added a right parenthesis - e.g., see below

.Range(.Cells([nSht3NextRow], Range("UserCost[Name]").Column)).FormulaLocal = ...

Before it was .Column).FormulaLocal and that syntax is corrected to

.Column)).formulaLocal

This is no help?

Dave

ASKER

Okay, I see that for the .Range that you did. Let me run that.

ASKER

I keep getting, the item with the specified name wasn't found. I checked the Table Names for both and they appear to be correct. Maybe I'll try renaming one, unless something else is occurring.

where is that error occurring for you? Do you get a clean compile?

Dave

I'm now getting that. I had to declare nIDXValue as Public Variant in a public module without the brackets and change all the [nIDXValue] to nIDXValue. I never got into the habit of using the [] evaluate brackets. Am I missing something? Let me understand that one so I can help.

Dave

ASKER

For this page yes, there were a couple of left over items, that I cleaned. But, this was fine.

ASKER

The biggest advantage of using the [ ]'s that they can be used as a defined name, either tied directly to a cell or range, or by itself. It's the easiest way of having variables available to all modules without having to declare them in a Public Variable, which can lose their value, if an error occurred, etc.

It's best use would be in a Database sheet, that was thought out and the variables, once assigned, just need the [ ]'s to recall their value. I have had one, maybe two, instances, where the Range("var") was needed, but that was rare. Unless, they are cleared out, they remain forever. Which one must remember to do, if the variable could be used for several conditions, like a User Name, etc.

I'll have to look that up sometimes. I did google on it and they frowned on it: http://www.excelforum.com/excel-new-users/719122-what-do-brackets-mean-in-vba.html

Here's your solution (I believe):

With Sheet4
                      .Activate          'Defined Names
                      .Rows([nSht3VisibleRow]).Hidden = False
                      .Rows([nSht3VisibleRow]).Interior.Color = 4210752
                       Application.EnableEvents = True
                      .Range(.Cells([nsht3nextrow], Range("Log[IDX]").Column), .Cells([nsht3nextrow], [nSht3LastCol])).Interior.Pattern = xlNone
                       Application.EnableEvents = False
                      .Cells([nsht3nextrow], [UserCost[IDX]].Column).Value = .Cells([nSht3LastRow], [Log[IDX]].Column).Value + 1
                       .Cells([nsht3nextrow], [UserCost[IDX]].Column).FormulaLocal = "= Log[[#This Row],[IDX]]"
                       .Cells([nsht3nextrow], [usercost[name]].Column).FormulaLocal = "=Log[[#This Row],[LABs]]"
                      .Cells([nsht3nextrow], [usercost[PID]].Column).FormulaLocal = "=Log[[#This Row],[Project_ID]]"
                      .Cells([nsht3nextrow], [UserCost[Date]].Column).FormulaLocal = "=Log[[#This Row],[Date Out]]"
                      .Cells([nsht3nextrow], [UserCost[Year]].Column).FormulaLocal = "=Log[[#This Row],[Year]]"
                    
           End With

Open in new window

nIDXValue is not declared anywhere, nor is it a named range. I get compile error using it. As a public variable defined, I get by ok. The shorthand sounds appealing, but I don't think I'm going to change my habits just yet. I do see the appeal in using it with tables, however (Very nice, that)- and perhaps range names and cell references. Seems like a throwback to the old Lotus 1-2-3 programming names. Wonder if you can build a formula with it?

I need to read up on how brackets are used.

Is it working for you now?

Dave

ASKER

Well, I have run into a number of instances where the Range("var") did not work and/or was cumbersome to use. It may be to each their own, but when I can easily manage the variables in one place and not have to worry about them getting lost due to an error or the Sub ending, etc. It just seems easier. I'm using defined names more than in the past, and not having to worry about whether they are declared Public or not.

There may be a drawback somewhere, and then one will need to adapt for that situation, but for 90% of one's code the [ ]'s are the way to go. Defined names (Name Manager) are talked about a lot, not used a lot. These just seem easier. Just my opinion though.

ASKER

Yes, I saw that and named it to a cell within the Database. It's that easy.

is [nIDXValue] a defined name?

Dave

ok - it wasn't defined in my copy

well, that's one way to see a list of all your variables on the spreadsheet :) I'm getting your approach. I'm not there, but I do get it and the variables are very static, yes?

:)

dave

ASKER

As far as building formula's, you can see that I use one variable to add upon another one. Yes, it's like some of the older programming that I used to do, where variables where in one place, text in another, etc. Personally, it seems better to manage them in one location.

I see it.

I've never seen this command either - and it doesn't compile. Is this another error? See BOLD:

Public Sub CommandButton1_Click()
'insert selected costs into Costs sheet
Dim i As Integer, ir As Integer, lR As Long, lC As Long
Dim lLastRow As Long, lLastVisRow As Long
Dim rngCostRow As Range, rngCostVal As Range
Dim CostInfo As Variant, rngCostCell As Variant, nIDXValue As Variant, mycell As Variant
Dim arrCost() As String
Dim arrUnits(12) As Double
Dim ctrl As Control

SetApp False
With Sheets("LogFile")
Set rngCostRow = .Cells(ActiveCell.Row, Range("Log[Cost]").Column)
ir = rngCostRow.Cells.Row
i = .Cells(ir, Range("Log[IDX]").Column)
End With

Also, another anomoly - you call Userform_Activate and yet when Load Userform is done, it runs the activate command. Did you know that the activate routine was being called twice in a row?

ASKER

No, that actually goes to a function that I forgot to include. Instead of using the:

With Application
.EnableEvents = False ...

I just wrote a function, which should have been with the module, but it makes the setting of the App Events easier.

ok - do you still need my help, or should I keep plowing thru this?

Dave

ASKER

That may be something to look at. What I can't figure out is why it won't recognize the Table on the Costs sheet?

Ok - I wrote that function and added [nIDXValue] to the database tab and now finally get a clean compile

I get recognition. Exactly what line is causing a non recognition? did you see the code I posted a few posts ago - I thought I fixed that.

dave

ASKER

Can you get the information to the UserCost Table, or I just renamed it CatCost to see if that made a difference?

I can and am in my version I've been patching...

ASKER

I renamed it back to UserCost and will try what you put together.

ASKER

Maybe there is something wrong with my Table, or maybe somehow it's tied to another book, but it will run down fine until the .Cells or the .Range, then it says it can't find it. The other Table is fine, but may need more testing.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

These two lines are redundant, one setting value then the next changing to a formula

There's NOTHING at #ThisRow on the Log sheet, so you're going to get #Value errors.

what exactly are you trying to put in the first so many columns of this new row?

Dave

ASKER

I'm getting an error in MyCell, trying to find it.

Well, look at the table - is it filled with #VALUE???

That could be why myCell is not rendering...

:)

dave

ASKER

When it was manually put into the cell, it seemed to sync, via #This row, with the other spreadsheet. As I changed the values in Log, the values were changed in UserCost.

well, its not the same row now, I don't think... Its adding an entire new row

ASKER

That is interesting, and it may be due to my changing the name of the Table, because in the original it was all in Table Syntax, not Sheet!Range.

Ok - it might be useful for me to see the original?

ASKER

It's suppose to add a row and then place the formula within the cells, if it's row 5 it may not come back with anything as there is no row 5 in the LogFile Log Table. My Question was how to get the table to accept formulas, without erroring out. I know it will work if placed correctly, because the others (1-4) worked. Just kept getting the not recognized errors.

I'm getting the formulas. Did you try my version of the workbook?

Dave

ASKER

This is how the original LogFile[Log],[Cost] formula looked like.

=IF(Log[[#This Row],[Year]]<1,0,SUMPRODUCT((Log[[#This Row],[IDX]]=UserCost[IDX])*(Log[[#This Row],[Project_ID]]=UserCost[PID])*UserCost[Total_Costs]))

ASKER

The MyCell was problematic. I was going to take yours and place it within my original, and see how that took.

ok - I'm working it - building knowledge around this table manipulation stuff. That formula does indeed work, and its in the LogFile tab

Riddle me this. Is the cost update supposed to be adding a new row, or modifying an existing row? Thanks

Dave

ASKER

The Cost update does not add a new row. The little oddity to this is some of the code will not work unless one is in the LogFile worksheet. You may have noticed, I don't know it it's included, but parts of the code specifically Activate Sheet3 or it won't run correctly. Although, I think those are the one's that transfer other formulas and it needs to have Sheet3 activated.

What I'm attempting to do will also rid the code of that, for the most part.

ASKER

Dave,
I ran the code on my original and it did not error out and seemed to place the formulas where they need to be. It looks like a success to me.

Ron

So I was working with a partial file?

ASKER

Yes, but I incorporated all those areas that were needed, as the other parts worked. I still don't know why it kept erroring out on my partial original. I had taken quite a bit of time to make sure that the other code, which is used for different purposes, didn't keep this from working. To have put in the entire book would have really confused things, and parts of it are confidential as well.

Ok - I was wondering why it felt like I was working in the dark. Yea, I got my version to finally popup the userform and accept changes...

Dave

Well, glad I could help. And, learned quite a bit about tables, just now. Kind of feels like Clipper (from the late 80's).

Dave

ASKER

What I just got a notice on, in deleting the Costs Row, was that it was set up as a Total Row. I need to check and find out how that happened.

ASKER

Well, as I just reported, there is still a lot about Tables that I need to learn, but seeing how they are integral to 2007 and 2010, I'm trying to learn as much about the syntax on these as possible. There really is very little written about them.

The best help for me was to use the Locals Window and in stepping through the code, seeing all that is available. That was how I came up up with my lLastRow formula. Not, that the (xlup) wouldn't work, but this is just as reliable and Table specific. It also keeps me Table (list Object) focused.

ASKER

Thanks for the quick response and the continued testing until it was tweaked just right. Although, I guess you really nailed it the first time. It just took me a while to figure it out.

Ron