Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Microsoft Office

61K

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

have this solution but then  need to count the coulmns with data

also
when an number appear need be color green the cell

5555555.PNG456778654.xlsm
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: 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!

each column have  numbers

V_OUT sheet is where to work with
 

example  03  in each column  
 show the count by column in row 6 above the column
then the total count in b6

AAAA23123.PNG674398_out.xlsm
0
I'm needing to round a date based on the timestamp within the field.  Right now, I have say "12/08/17 16:42:05" in a given cell.  I need to round this date to either 12/08/17 or 12/09/17, based on the time.  So if an order came in at say 3pm (15:00:00), I need the date to round to the next day, since that's after hours. Make sense?
0
I'm tying to calculate fiscal years between dates using the formula below with an error. Thoughts

=IF(AND(G2>=Date 10/1/2001,G2<=Date 9/30/2002,"FY2002","None"))
0
Hi has anyone used the above method to calculate a body surface area in Excel using the Mostellar method ? I have looked on Google and the formula is:
Mosteller Method:[1]

BSA (m2) = (height (cm) x weight (kg)/3600)½

eg, BSA (m2) = square root of (height (cm) x weight (kg)/3600)

Using their example a height and weight of 121cm and a weight of 25 the BSA calculates at 0.92.
On my formula in Excel: =SQRT(X172*N172)/3600 I get 0.12.  
Any suggestions please/

Thanks
0
I have a lengthy Excel VBA script that works quite well. I have recently added to it the VBA code to send certain worksheets by email to selected individuals via Outlook from inside Excel VBA. Now, I want to add code that will call six separate one-page Word documents (already written and saved to disk as six .docx files) and print 1 copy of each of these six single-page Word document files at the end of the Excel VBA script, just before the script terminates and Excel closes. I've never called a Word document to print from within Excel with VBA, but, like with Outlook, I know it can be done.

Do any of the experts have a code snippet or sub that I could try within my Excel VBA script that would do this?

Thanks!

Glenn
0
How can I enable file version history within Office 2016 applications (such as Word, Excel, PowerPoint, etc) so that I will be able to go back to previous revisions of the saved document?

I need to be able to look at previous revisions of saved Office 2016 documents within Word & Excel.
0
How can I enable previous file versions within Office 2016 applications (such as Word, Excel, PowerPoint, etc) so that I will be able to go back to previous versions of the document?

I remember this used to be enabled by default but now I don't see this feature enabled.
0
Grab First Two Numbers
If the cell's first three characters are 01-, I want them to be picked up.
If the cell is blank, I want the function to show 00-.
Example-of-Function-Desired.jpg
0
I need to check in an excel 2013 sheet the following
    Column  F has desired values  (in multiple rows)
     Column C has extracted values  (in multiple rows)

I need to make sure each cell in column c is one of the value in column F
I could try  formula  =IF(COUNTIF(F3:F14,C4),"Yes","No") in column D  and it works
But the problem is, When I copy formula to next row, say row 5 the range also increments
 On row 5, I SHOULD get =IF(COUNTIF(F3:F14,C5),"Yes","No")
But Instead I am getting now on row 5 =IF(COUNTIF(F4:F14,C5),"Yes","No")  - The range also increments after copy

Is there an alterante solution?
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!

I inherited a bunch of workbooks that each contain over 60 sheets, each  containing over 100 thousand Index/Match formulas. It runs pretty slow...

The owner wants to increase performance, but loves his formulas that update on the fly. I was thinking, if I could give him a User Defined Function that runs faster than Index/Match, that would get him part of the way there. Is this possible? If so what would be the process/VBA code?

It would be pretty cool if we could create UDF's that are more efficient than what Excel provides.
0
I recorded and quick macro on an ActiveCell, and it recorded this huge multi-line Sub.  How would I create a function that I could use to do this calling it: CenterAndBorders?  I first need to shorten it up, it added way too many lines, with some that are not even used.  Please advise and thanks.

    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
 

Open in new window

0
I have 3 variables to check against, but some of them require other checks and I am getting lost. See file attached; this is a follow-on query and relates to rows 22-35. I have included in the spreadsheet an image of the calculations I am trying to automate. I believe i need to validate in the order i have labelled them in the image - 3 cases. In essence I am trying to derive an S value for the 3 cases. I haven't built Eq1 because i though i could build it into an IF statement but now i am not too sure. Hope this makes sense.
2nd-check.xlsx
0
I received a bunch of workbooks that contain 40-80 worksheets full of data that all refer to each other. It's a mess. I would like to quickly understand what each sheet refers to. Will you please let me know what VBA code I can use to do the following: list each sheet followed by a list of other sheets it references too.

Please also include links/sources. Also many of the sheets contain over 30 columns with over 100,000 rows of data. If possible I would prefer a way to achieve this without iterating through each cell, as doing so, might take a very long time.

Example:
1: Sheet1<--- Sheet3
2: Sheet1<--- Sheet5
3: Sheet2<--- Sheet10
4: Sheet5<---Linked Sheet1: C:\Other\ExtraFiles\Backupsheet.xls

1: Says that Sheet 1 contains data from Sheet3
2: Says that Sheet 1 also contains data from Sheet 5
3: Says Sheet2 contains data from sheet 10
4: Says sheet5 contains data from sheet 1 of a linked workbook located at the following path: C:\Other\ExtraFiles\Backupsheet.xls
0
Here is my example ABRAME124001 the answer should be ABRAME1.  

Thank you!
0
Not sure my logic is correct with this, but I thought i would give it a go. I have used an IF OR statement and then on the true variable embedded an IF AND statement. Within the attached spreadsheet, cells D13 and D14 I check 1st for validity - in the example I have given, the values fall within the true range so should then calculate the exponential - this is not happening. In cell G16 I have checked what I believe the output should be for the below. Within the worksheet I have embedded an image of the calculations I am trying to automate. i decided to start with those circled in red with 1 and then check 2....

=IF(OR(D13<-1.5,D14>2),0,IF(AND(-1.5<=D13<=0,0<=D14<=2),D16*EXP(D17*D13),0))

Thanks in advance, Paul
co.xlsx
0
Hi, I have been asked to modify the attached workbook but the first tab is locked - the creator is no longer in the company.  Can anyone help me unlock the sheet so I can edit it?  

Thanks so much

Pippa
Cost-Capture-Lines-ZBB.xlsx
0
I have to open csv file after creating from Powerbuilder.
Upon creating csv file , I have to open csv file as excel file automatically.

So I think it is better to use command prompt.
How could I open csv file as excel by using command prompt?
0
We need to change a Mac Office 2016 user's Mac Office 2016 license from Retail to Office 365. This is because the retail license doesn't have all of the features that the Office 365 license has.

The user who needs this has already been assigned an Office 365 license but at the time he installed Office 2016 on his Mac (around a year ago) the retail license was used.

Is there an easy way of converting the Mac Office 2016 license from Retail to Office 365 or will we need to completely uninstall Office 2016 Retail and then download and install the Mac Office 2016 Office 365 version?

MAC-OFFICE-LICENSE
1
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

is it possible to hide distribution groups or list on per email box instance  so the group is not visiable when looking at the Global Address List?

we have some distribution groups that not everyone needs to see or to be able to select to send to
0
I am using outlook 2016, and not sure why, but a lot of emails are not displayed correctly.
As you can tell, just looking at the email in the bottom view pane, it's not displayed correctly.
Same thing when I open it.  The actual email in a way is centered, but why is there is so much grey padding
on each side of the email?  I am using a 4K monitor and my resolution is full 4K.  What's strange is, most of the emails are fine,
it's just some that are like this, maybe 25% of them. Perhaps it's only happening to the emails that are using HTML, and if that's the case, why is that?

outlook1
outlook2
0
After moving an email (from say the inbox) to a newly created folder, the email disappears.

What would cause this?

I checked the sync settings and they seem correct.
0
One user cannot open a saved excel xlsx file. Other users can. And the user can open other excel files. Wet to backup files before problem, still cannot open, other users can. Error popup box says Could not open 'G:\2018 accounting worksheets\dailycheckbook 2018.xlsx'  and in blue small type WAS THIS INFORMATION HELPFUL?    Repaired office 2010, File security .  to recap, 1 user cannot open 1 file, the spreadsheet only has 8 lines and 10 columns and 1 link to last years ending total. Other people can open the file and it works correctly, the user can open other spreadsheets. All users are using the same version excel. 2010. The Backup copy and 2 days prior get the same message.  deleted the link, and the problem continued. Rebooted, same issue.
0
Hi,

I need your help with an excel formula that would retrieve the column letter from another sheet inside the same workbook that have specific header name.

Let me explain;

In the workbook, i have 2 sheets: Test1 and Dbase.

Sheet Test1 will have headers that could start at any rows. In below case, the header are at row 4.
Test1 sheet sample

Then I have the Dbase sheet that will have the same header names in column B, I will tell also the sheet name that I want to pull the column letter from in column D, and also the Row headers in column E where the headers are positioned in the sheet from the column D.
Dbase sheet sample
Now, I want to have a formula in column C that will pull the column letters for each headers.
The result will look like this:
Final result

I have some formulas like : SUBSTITUTE(ADDRESS(1,COLUMN(),4,”1”,””) and also UPPER(CHAR(COLUMN()+96)), but this is only applicable when retrieving from the same sheet.

How can I do this?

Thank you for your help.
find-column-letters-from-another-sh.xlsx
0
In a spreadsheet cell I can enter =FIND("M","abcdenfgMnc")  and it gives me 9

If I have this      res = Find("M", "abcdenfgMnc")
in a Public module it says compile error  Sub or function not defined

Get the same error when I put   ? Find("M", "abcdenfgMnc")
 in an immediate window
0

Microsoft Office

61K

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.