?
Solved

How To Write This in 2007 Syntax

Posted on 2011-02-28
52
Medium Priority
?
231 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Cook09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 27
  • 25
52 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35004502
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

0
 

Author Comment

by:Cook09
ID: 35004591
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004600
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
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:Cook09
ID: 35004607
Okay, I see that for the .Range that you did.  Let me run that.
0
 

Author Comment

by:Cook09
ID: 35004620
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004625
where is that error occurring for you?  Do you get a clean compile?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004634
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
0
 

Author Comment

by:Cook09
ID: 35004639
For this page yes, there were a couple of left over items, that I cleaned.  But, this was fine.
0
 

Author Comment

by:Cook09
ID: 35004692
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004723
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

0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004779
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
0
 

Author Comment

by:Cook09
ID: 35004784
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.
0
 

Author Comment

by:Cook09
ID: 35004789
Yes, I saw that and named it to a cell within the Database.  It's that easy.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004791
is [nIDXValue] a defined name?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004795
ok - it wasn't defined in my copy
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004806
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
0
 

Author Comment

by:Cook09
ID: 35004808
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004816
I see it.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004827
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004836
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?
0
 

Author Comment

by:Cook09
ID: 35004837
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004842
ok - do you still need my help, or should I keep plowing thru this?

Dave
0
 

Author Comment

by:Cook09
ID: 35004847
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?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004851
Ok - I wrote that function and added [nIDXValue] to the database tab and now finally get a clean compile
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004860
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
0
 

Author Comment

by:Cook09
ID: 35004865
Can you get the information to the UserCost Table, or I just renamed it CatCost to see if that made a difference?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004869
I can and am in my version I've been patching...
0
 

Author Comment

by:Cook09
ID: 35004875
I renamed it back to UserCost and will try what you put together.
0
 

Author Comment

by:Cook09
ID: 35004901
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.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35004909
Here's my version:

Dave
TableSyntax-r4.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004936
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
0
 

Author Comment

by:Cook09
ID: 35004940
I'm getting an error in MyCell, trying to find it.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004947
Well, look at the table - is it filled with #VALUE???

That could be why myCell is not rendering...

:)

dave
0
 

Author Comment

by:Cook09
ID: 35004955
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35004961
well, its not the same row now, I don't think...  Its adding an entire new row
0
 

Author Comment

by:Cook09
ID: 35004983
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35005000
Ok - it might be useful for me to see the original?
0
 

Author Comment

by:Cook09
ID: 35005007
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35005021
I'm getting the formulas.  Did you try my version of the workbook?

Dave
0
 

Author Comment

by:Cook09
ID: 35005038
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]))
0
 

Author Comment

by:Cook09
ID: 35005052
The MyCell was problematic.  I was going to take yours and place it within my original, and see how that took.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35005056
ok - I'm working it - building knowledge around this table manipulation stuff.  That formula does indeed work, and its in the LogFile tab
0
 
LVL 42

Expert Comment

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

Dave
0
 

Author Comment

by:Cook09
ID: 35005227
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.
0
 

Author Comment

by:Cook09
ID: 35005319
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35005336
So I was working with a partial file?
0
 

Author Comment

by:Cook09
ID: 35005410
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35005413
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35005418
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
0
 

Author Comment

by:Cook09
ID: 35005428
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.
0
 

Author Comment

by:Cook09
ID: 35005453
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.
0
 

Author Closing Comment

by:Cook09
ID: 35005472
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
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question