Microsoft Office

59K

Solutions

40K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have the below code that works fine, however i am trying to amend th code to no avail so that it saves the files with the leading Zeros.

the number element are store numbers and it ranges from 1 - 168

ideally if possible can you advise how do i change the code so it saves the output files like the below example if a store number is 2 digits and the 3 digits etc.

0001
0010
0120

Sub GenerateOutput()

    Dim i As Long
    Dim iGradeRow As Long
    Dim iGradeCol As Long
    Dim iPosSeqRow As Long

    Dim s(1 To 7) As String

    Dim aGradeData() As Variant
    Dim aPosSeq() As Variant

    Dim aOutput(1 To 500000, 1 To 12) As Variant
    Dim iNextOutputRow As Long

    Dim ExportWorkbook As Workbook

    Dim Site As String
    Dim Department As String
    Dim Category As String
    Dim ArticleGrade As String
    Dim dp As String
    Dim ct As String
    Dim posQty As Long
    Dim y As Long
    Dim lrStores As Long
    Dim recordId As Long
    Dim selId As Long

    '------------------------

    Application.ScreenUpdating = False

    ' Get arrays of data to loop round
    With ws_Grades
        aGradeData = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Value2
    End With
    With ws_PosSeq
        aPosSeq = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 20).Value2
    End With

    s(1) = "( "
    's(2) = iGradeRow - 3
    s(3) = " / "
    's(4) = UBound(aGradeData, 1) - 3
    s(5) 

Open in new window

0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I use outlook 2016. Can i set Out Of Office Automatic Replies without an exchange server?
0
Columns("B:H").Select
    Selection.Delete Shift:=xlToLeft

I used above code to select columns from B to H and delete it. But its getting select from A to I and deleting all from A-I.

Please advise me code correction.
0
Folks,
Several years ago EE helped me develop a monster of a workbook. I am attempting to create a similar workbook and have some of the code completed. However, every time I open the workbook, I get the following error. I need help so I can move forward, please.  The file I am working with is attached.

Excel ErrorExcel-for-Business-Analysis.xlsm
0
Hi

I need to create an Excel CSV file.  This CSV file has a column of large numbers .....  example 2100000000003   .

I wish for the numbers to be represented in full view.   I need to see the full number so that I know that each number is unique.
 Excel insists on changing the  number to scientific notation.  Of course, they all look the same then.

The application accepting this CSV file will read the numbers as text,

Question... How do I save the CSV file so that I open it again,  I will see the full number instead of the scientific notation representation.

Thank you.
0
I wanna save my user ID and password in excel, but i dont want people to see my password. I wish to convert password to ****** .

sample is attached.

Thank you in advance.
0
I would like to write an excel macro script that will extract data from a spreadsheet and convert it into a tuple format, combining the first column value with each column value individually. A sample input file is provided.

Example:

Given array:
a1 b1
a2 b2 c2 d2
a1 b2 e3
a4 b4

The VB script should generate a column that looks like this:
a1 b1
a2 b2
a2 c2
a2 d2
a1 b2
a1 e3
a4 b4


Thank you, experts!
Sample-Input.xlsx
0
Please look at the following VBA code that relates to the attached spreadsheet. The relevant section of this sub is the second paragraph where the first line comment is 'Delete all data in the "TME" tab older than one week. The other paragraphs of this sub work fine as far as I can tell.

This section of the sub refers to the attached sheet, which is the TME tab of a much larger workbook. As you see, the intent of this section of the sub is to delete all records on the TME sheet older than one week from TODAY (the date in column I, Row 1 or relative cell 1,9). For reference, the formula in I1 is =(TODAY()-MOD(TODAY()-1,7))-3. This formula works fine - no need to change it.

The problem is that this second paragraph of the sub below does not actually do any deleting of rows. It also does not stop execution for any errors, but proceeds right on to the next section of the sub without doing anything. I want it to delete all rows from row 2 of the attached sheet and continue deleting until the date in column A is one week older than the date in cell I1.

What needs to be changed to this section of the sub to get it to properly delete the required rows?

Thanks!

Glenn S.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub sub1()
'Import the daily data from the Cognos extract
Dim rng As Range, region As Range, col As Range
Dim rcount As Long, r As …
0
Please see attached file. In a nutshell I want to create a filter on a cell in one sheet, that will look at another sheet and filter rows on that value, and position me on that other sheet.
sample.xlsx
0
I want to use formulas (rather than filtering) as I'd like this to work automatically. I have a long list of cells each assigned a category. I would like to create another column with the category names only (without duplicates). Later I would use these category names in other forulas.

The attached file shows my current solution. The problem is that I think it's quite inefficient, so it runs rather slowly. For every line in the table it checks every previous line to see if the current category was previously seen.

I'm not familiar with pivot tables, but those might work as well.
category.xlsx
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Only a quick question, trying to ascertain the best practice to deal with leavers.

Q: What is the best policy for leavers and retaining email, Auditing etc?

We have quite a few leavers on the project but need to retain all email for these leavers.

We have a pure Office 365 environment.

What are the implications on journals after deleting the users account, soft and hard delete.

Thanks

John
0
Have this data need query P4
exact macth
with find button  in column S
 then create the P4 output sheet

SHOW Column  d, E, F, G, H ,I, J,K,L,M,N,O,p.q.s.

13 set
show selectED in yellow BACKGROUND
n4mKKK_2A4.xlsm
0
Hi,  When I enter something new or change something in Cell B1, I would like Cell C1 to be shaded yellow.   I tried this with Conditional Formatting, but I believe that you need to have a specific value in order for conditional formatting to trigger the change.  I want this shading in C1 to be triggered if anything is added or changed in B1.   In other words, B1 could be blank or already populated, so if there is any change entered to that cell, I need C1 to alert me by shading it yellow.  I then need to copy it to subsequent rows.  

I hope my request makes sense and is clear.

Thank you!
0
need clean the data in form
29050695_form_A.xlsm
0
after select week number
29050658--1-BB.xlsm
0
I need to ensure ALL mandatory cells in a row are filled in before another row is added.  Please see attached file for example.  
I've intentionally left blank columns I, O, R and S.  
Before I add a second row, I'd like to see an error message identifying the unfilled cells, or even better those unfilled cells highlighted.
If this is not able to be done, any suggestions are most welcome.
EE-Assets-Multi-Road-Template.xlsx
0
need have an week  in c9 form data
Adriana--8-A.xlsm
0
Greetings,

I have a matrix participants in a women's mentorship program will be filling our regularly. The rows have "+" and "-" and "0" as valid entries.

See attached XLS

I have static values in-place right now; the calculation is based on
"+" = +1
"-" = -1
"0" = 0

Can you help me with a formula to calculate?
Thank you!
JohnD
ULP-matrix.xlsx
0
Folks,
I have a command button that I want to use to check for changes before closing and exiting Excel. What's the correct VBA syntax please?
0
Office 365 Training for Admins - 7 Day Trial
LVL 2
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Hello experts,

I would like to have outlook 2010 auto delete any unread emails in my inbox after 30 days. Is there a simple way to set this up? I have attempted to create a rule, but no luck. This is on an exchange email server, getting emails through Outlook 2010 Exchange account.

Any assistance to get this automated would be greatly appreciated.
0
I'm just looking for affirmation here...

I want to create a shared calendar and we have Office 365 and use Office 2016.  So, of course, the first step in creating a shared calendar is to create an email account.

So we created a public folder mailbox in the Exchange admin center.  We then created a Root Public Folder... I gave myself owner permissions.

Now I'm told that I need to go to Outlook and view Public Folders and if Public Folders isn't visible to give it a few hours.  This makes sense as it's Microsoft and with Google it takes seconds.

Anyway... can someone confirm how many "hours" it takes until I can see Public Folders?

Thanks
0
I have an Excel table with columns A-H, rows 2-100. It is filled with text data (Application Names). I'm looking to populate column H with a list of common applications that are present in all 6 columns of the table.

Duplicate value formatting doesn't work as it identifies anything that appears more than once, where as I'm looking only for what is present in all 6 columns.
0
1-when hit button one time need show 1 with red background (Format)
2- when hit button 2 time need show 2 with red background (Format)

here the file
to add the code

Thanks in advanced
buton.xlsx
0
I use VBA to create custom toolbars for MS Office applications.  For Word 2007, 2010, 2013, and 2016, I've always deployed global templates as a .dotm file in the user's AppData\Roaming\Microsoft\Word\STARTUP folder.  For Excel and PowerPoint, either .xlam or .ppam in the AppData\Roaming\Microsoft\AddIns folder.  

Clients are asking me whether these same tools can be made to work in Office 365 where the applications are cloud-based rather than installed on the local machine.

Do global templates and Add-ins work with the cloud-based versions of O365 applications?  Can they be deployed as-is, or do they require modification, or do they need to be recreated in an entirely new language?  Can anybody point me to some documentation on this topic?

Thanks
0
I need a vlookup that keeps the result in date format. I am using the following vlookup:

=VLOOKUP(C2,Sheet1!C:I,7,FALSE)

The result should be a date. The result is showing some very random looking number. How can I keep this in a date format? Thanks!
0

Microsoft Office

59K

Solutions

40K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.