UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Issues With Formating A Table Cell..., Office 2013    
 
   
Miguel_A
post Oct 24 2019, 02:11 PM
Post#1



Posts: 250
Joined: 20-January 04



Hi

So i have a table created in VBA with some columns, however when trying to format the body of the table (on creation, just to format the first row, cause all the other's rows should be formatted automatically wit the ones before) it gives an error. The one way i did find that would not create any error is using the direct method with out the need of the table object, however that must be wrong cause i am sure there is a way of doing this using the table object... ws.Range("B2").NumberFormat = "yyyy/mm/dd"

This is what i have attempt so far:

.Range("TabelaDados[Data]").NumberFormat = "yyyy/mm/dd" 'gives the error - Invalid procedure call or argument
.ListColumns("TabelaDados[Data]").NumberFormat = "yyyy/mm/dd" 'gives the error - Subscript out of range
.ListColumns("Data").DataBodyRange.NumberFormat = "yyy/mm/dd" 'gives the error - Subscript out of range
.ListColumns(Cells(2, 2)).DataBodyRange.NumberFormat = "yyy/mm/dd" 'gives the error - Subscript out of range

did create a small macro where you can can test yourself.
Hope there is a solution for this cause i am out of ideas...

CODE
Sub makeTable()
    Dim ws As Worksheet
    Set ws = Worksheets(1)
    
    ws.Cells(1, 1).Value = "ID"
    ws.Cells(1, 2).Value = "Date"
    
    Dim tbl As ListObject
    Set tbl = ws.ListObjects.Add(xlSrcRange, Range("A1:B1"), , xlYes)
    
    With tbl
        .Name = "DataTable"
        .TableStyle = "TableStyleMedium2"
        .Range.AutoFilter
        .Range.HorizontalAlignment = xlHAlignCenter
        .Range.VerticalAlignment = xlHAlignCenter
        
        .Range("TabelaDados[Data]").NumberFormat = "0" 'Invalid procedure call or argument
        '.ListColumns("TabelaDados[Data]").NumberFormat = "yyyy/mm/dd" 'Subscript out of range
        '.ListColumns("Data").DataBodyRange.NumberFormat = "yyy/mm/dd" 'Subscript out of range
        '.ListColumns(Cells(2, 2)).DataBodyRange.NumberFormat = "yyy/mm/dd" 'Subscript out of range
    End With
End Sub


Thanks in advance
Go to the top of the page
 
arnelgp
post Oct 24 2019, 02:31 PM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


how do you make those Square brackets on the table name.
I can't recreate them.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 02:37 PM
Post#3



Posts: 250
Joined: 20-January 04



check this image and see the 8 and 9 key from the keyboard
https://en.wikipedia.org/wiki/Keyboard_layo...rkey_f_yeni.svg

Uou need to press this 2 keys at the same time -> Alt Gr + 8 to access the third function
Go to the top of the page
 
arnelgp
post Oct 24 2019, 02:41 PM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


maybe that is why you are getting
the error.
did you also use the combination keys
when you type the formula on your code?
This post has been edited by arnelgp: Oct 24 2019, 02:43 PM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 02:46 PM
Post#5



Posts: 250
Joined: 20-January 04



you are not getting any error ?
Go to the top of the page
 
June7
post Oct 24 2019, 02:47 PM
Post#6



Posts: 1,012
Joined: 25-January 16



I used macro recorder and got:

Range("Table1[Column1]").NumberFormat = "m/d/yyyy"

When I try Data it fails.

This excludes headers:

Range("Table1").NumberFormat = "m/d/yyyy"

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 02:51 PM
Post#7



Posts: 250
Joined: 20-January 04



It's [Data] instead of [Column1] because you did not rename your headers
Go to the top of the page
 
June7
post Oct 24 2019, 03:01 PM
Post#8



Posts: 1,012
Joined: 25-January 16



Okay, now that works as well.

Maybe this does have something to do with how [ ] are typed. But surely you have other functional code with [ ]'s?

This post has been edited by June7: Oct 24 2019, 03:03 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 03:14 PM
Post#9



Posts: 250
Joined: 20-January 04



Like i said ... no idea why this don't works.
Did try with other's code (has you can see with the examples on the main topic)
And any of them work.
Go to the top of the page
 
June7
post Oct 24 2019, 03:32 PM
Post#10



Posts: 1,012
Joined: 25-January 16



Try without [ ] just to see if it makes a difference.

Range("TabelaDados").NumberFormat = "m/d/yyyy"
This post has been edited by June7: Oct 24 2019, 03:33 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 03:38 PM
Post#11



Posts: 250
Joined: 20-January 04



with .Range("TabelaDados").NumberFormat = "yyyy/mm/dd" it gives the error Run-time error '5': Invalid procedure call or argument
This post has been edited by Miguel_A: Oct 24 2019, 03:39 PM
Go to the top of the page
 
June7
post Oct 24 2019, 03:45 PM
Post#12



Posts: 1,012
Joined: 25-January 16



Sorry, got nothing.

I tried with and without ws object variable. All works.

If you want to provide workbook for analysis, follow instructions at bottom of my post.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 03:50 PM
Post#13



Posts: 250
Joined: 20-January 04



finally i got it working with .Range(2, 2).NumberFormat = "yyyy/mm/dd" ...
However if i try with named range still does not work
Go to the top of the page
 
June7
post Oct 24 2019, 05:16 PM
Post#14



Posts: 1,012
Joined: 25-January 16



This time I tested your full procedure, not my macro recorded code. It errors on that line because there is no TabelaDados[Data], it is DataTable[Date]. Also, the dot in front of Range is not needed.

Range("DataTable[Date]").NumberFormat = "0"

This post has been edited by June7: Oct 24 2019, 05:18 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 05:46 PM
Post#15



Posts: 250
Joined: 20-January 04



Hi

And did you tested the code after you made the alterations ?
That was a mistake from me when creating the new small code for testing and placing here ?

Anyway if you made the alterations you said and if you tested i am sure it will give you some kind of error like object does does exits or method does not exist.
Cause Range is a method from from something, in this particular case from a table that is a method as well from something else and in this case from Worksheets.
https://docs.microsoft.com/en-us/office/vba...worksheet.range

The dot there is to use with the with statement that indicates everything inside that with will belong to the whatever we write in front of the with.
https://docs.microsoft.com/en-us/dotnet/vis...-with-statement

Did you tested that after the alterations ?
Go to the top of the page
 
June7
post Oct 24 2019, 06:30 PM
Post#16



Posts: 1,012
Joined: 25-January 16



Yes, after modifying code as described, it works.

I know about With block.

You need to specify worksheet with Range using A1-style referencing if you want to act on other than active worksheet. Same for Cells(r, c) referencing. So if there is doubt as to which worksheet is active, use explicit reference. Does not even require declaring a variable.

With Worksheets(1)
...
End With

Also, as I understand, Range and Table names are referenced independent of worksheet. They are elements of workbook.
This post has been edited by June7: Oct 24 2019, 07:09 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Miguel_A
post Oct 24 2019, 07:05 PM
Post#17



Posts: 250
Joined: 20-January 04



Hi

humm i tested it and you right it works... i must have some other typo on it cause the first time i attempt it gave an error.

About the Range it seems there is an application Range (it seems they call it object grrr) as well and that is why it works. Was not aware of this range
https://docs.microsoft.com/en-us/office/vba...l.range(object)

it's confusing having so many things with the same name lol
Go to the top of the page
 
Debaser
post Oct 25 2019, 08:05 AM
Post#18



Posts: 156
Joined: 11-October 18



You might as well use the table since you have it:

CODE
tbl.ListColumns("Date").DataBodyRange.NumberFormat = "yyy/mm/dd"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 06:23 PM