Microsoft Excel

131K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

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

Sign up to Post

A Terminal Server running Windows Server 2012 R2, preview in Explorer show Word, Power Point and PDF documents without problem. But when I select a Excel document, I get the message no preview available. How can this be fixed?
0
Free Tool: Port Scanner
LVL 9
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi,

I can able to open chrome in vba but not able to click a button.

Could some please help me on this?

Button ID: tab-1195-btnInnerEl

Sub click()

'Set objgc = CreateObject("ChromeTab.ChromeFrame")
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" & " -url https://connect.ihs.com/home")
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" & " -url https://autoinsight.ihs.com/")
Application.Wait (Now + TimeValue("00:00:15"))
'objgc.Document.getelementbyid("tab-1195-btnInnerEl").click

End Sub
0
Hey EE, I'm currently a little over my head in my a project for my work, what I'm trying to do is make an excel spreadsheet where after inputting how many of each product we need to make to reduce excess prep and wastage, for example 200 Ham Salad Sandwiches, the spreadsheet would in turn say how much of each ingredient would need to be prepped, each of our products have a set portion based off grams, then work out how much of that ingredient would fit into our Prep Tubs and how many tubs we would require each day, I've only recently started using Excel after about 10 years so my knowledge is spotty at best, any assistance would be appreciated.
0
The macro below converts a column of false dates in Column B to real dates in a spreadsheet. I would like to expand the functionality of this macro to convert several columns of false dates in a spreadsheet.

Sub Metrics_PubWOs_RealDates()
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
With ActiveSheet
    Set rg = .Range("b2")
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    For Each cel In rg.Cells
        If Application.IsText(cel.Value) Then
            If IsDate(cel.Value) Then cel.Value = CDate(cel.Value)
        End If
    Next
End With
End Sub

Open in new window


So in addition to converting false dates in Column B, I would like this macro to also convert dates in Columns D, F, H, J, L, N, P, R, T, V, X, Z, AB, AD, AF, AH, AJ, AL, and AN. (Note that all columns requiring this macro to be run has a column header with the text string "Col", in case it's easier to write VBA code using this criteria instead).

I have attached an example of the spreadsheet.

Thanks,
Andrea
EE_MultipleRanges.xlsm
0
I had this question after viewing Find 10 closest x,y coordinates between two huge x,y data set.


I have an array of XY points corresponding to XY location on a grid.   There may be >100,000 entries.
I  want to compare the distance defined by Sqrt((X-Xa)^2 + (Y-Ya)^2) for a given point to every other point (row ) in the array and count the number with a distance less than some value I can define.
The output would just have the counts added to the XY data into another column for each row.
I'd need to do in excel.
0
NEED TO DELETE ROWS A-O in every csv in a directory , need a vba code to do this
0
Have this data in P4 sheet (output sheet, after selected data )
exact macth

but need crate the P4_VER sheet
so i can select the data needed
after ouput is show in P4

then
SHOW Column  d, E, F, G, H ,I, J,K,L,M,N,O,p.q.s.
13 set
show selectED in yellow BACKGROUND
of the selectes DATA as needed
29050744--1-_A_n4KKK.xlsm
0
I have 80 records with say columns 1-8 in worksheet 1. In worksheet 2, I have copied (linked) say columns 1-3 into worksheet 2. In worksheet 2, I then add columns  4-16 repenting the months Jan-Dec.

Now everything is OK until somebody sorts worksheet 1. Within worksheet 1 we use a value to assess priority and sanction projects. It is nice to sort ascending and we graph this. However, within worksheet 2 (when filter applied) the 80 records are adjusted for columns 1-3 but not for 4-16. I am surprised at this, but when I thought about it you are only linking back to data and not associating with the row in worksheet 2. Am I using the right approach - linking. I do not want to turn of filtering etc in worksheet 1.  Thanks in advance Paul.
0
Folks I keep getting an error on this code at:
.lstCategory.ListIndex = 0

Open in new window

in the below subroutine when the workbook opens and I haven't figured out what is causing it?
Private Sub Workbook_Open()
Dim intIndex As Integer

With Worksheets("Main Menu")
    For intIndex = 10 To .UsedRange.Rows.Count
        .Cells(intIndex, 1).Value = ""
        Next
        .Activate
        .lstCategory.ListIndex = 0
End With

Open in new window

Error opening workbook
0
Good morning, I will like a macro to  hide columns and rows. Always in the first column. If the cursor is in the 12 row and we Control +z should hide the row 1-11 and all de blank columns. The same if we put the cursor in row 55, hide from 1-54 and all the blank columns. In same cell there are X, so this column should be displayed.
Than you very much for your help.
0
Free Tool: Site Down Detector
LVL 9
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
Dear Experts,

I really need your help in applying the formula to make the Code sheet working.

I have attached the sheet for your reference.

As per the sheet I need to generate code according to combo box selection in various columns and its references are located in different sheets.

I have applied the formula but it does not work as expected.

Can anyone help me on this task?

(Remark : I have used only Index and Match functions. You may suggest VBA if required)

:)
Regards,
D Patel
Code-Template.xlsx
0
Hello All,

How we can set the path in VBA while using MAC PC. For ex. like we do in Windows

sub Test()
                   mypath$ = "C:\Desktop\FilesIn\"
                   newpath$ = "C:\Desktop\FilesOut\"
End Sub

Same function we want to use in MAC PC

locations of Mac PC is   "\users\desktop\test\FilesIn" & "\users\desktop\test\FilesOut"


Thank in advance
0
Dear Experts:

I would like to extract the substring located between the 8th and the 9th underscore from the right, i.e. 90-235-58-22

I got a user-defined function for that, but I also would like to have a formula. Hope this is feasible!

G:\KLS_Allgemein\DMD\PM_2_3\IFU_Bos_Driver_90-235-58-22_REV_2_RD_2016-02_5s_SF_S24_bw.pdf

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Hi Everyone

I am having a problem opening Hyperlinks in Excel 2016 running on Windows 10.  I have also replicated the problem on some of our other machines that are running Excel 2013 on Windows 8.1.

Hyperlinks that link to other Excel workbooks do not open.  No error message or warning appears, and all that happens is the application hangs with the spinning blue circle.

Hyperlinks to other documents such as Word files open without a problem.

When I re-create a hyperlink to an Excel sheet in say Word 2016.  I get a warning message to say it could be potentially unsafe and then the Excel sheet opens.

I have tried hyperlinks to other spreadsheets on my C:\ and also to network shares, neither will open.

I have checked EventViewer but I haven't been able to identify any issues there.

We are using Sophos Central Anti-Virus, but I haven't come across this issue when using the product elsewhere.

The only other thing we tried is a registry fix to turn off the "potentially unsafe" message, which now doesnt appear, but we were having the issue before that anyway.
0
how to increase characters in the kutool vbscript
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
In the attached file, the change in percent is not calculating correctly in the array. For example,

First, in the Baby Bear extract, the percent of change from row 7 to row 6 is only shown as 1%

Second, the percent is not showing as negative when the volume decreases.

Third, I'm not sure the correct formula for percent change is accurate:

For Percent Change:
New number - Original number = Difference
Difference / Original number = Answer
Answer x 100 = Percent Change

The "times 100" part might not be necessary, since we are using the percent number property in the Change in Percent column cells.
Disney.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!

Hi experts! I have a spreadsheet that I use that contains a pivot table and a slicer. This spreadsheet worked fine with Excel 2010, but my company recently upgraded to 2016 and since the upgrade anything that is done with the slicer throws the error "Microsoft excel has stopped working" and closes out. I was able to disassociate the pivot table from the slicer and delete the slicer. Once I recreated the slicer the same error returned. I checked and disabled any add-ins. I've checked and ran all updates since the office upgrade.  I've also ran an office repair. i'm wondering if there may be a setting turned on/off in 2016 that was not in 2010 that is causing this issue. I don't know, I know enough to be dangerous, but this has been scratching my head. Please help.
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
Hi all,

I'm trying to convert an Excel macro to work in Google Sheets. I have neither Excel VBA nor Javascript skills, so I'm hoping someone can help.

Here is the original Excel macro code:

===========================
Sub RoundRobin()
    Dim x, y, Counted, Number
    Set wsSheet3 = ThisWorkbook.Worksheets("Sheet1")
   
    Number = wsSheet3.Range("B2").Value
    x = wsSheet3.Range("A" & Rows.Count).End(xlUp).Row
    For Each c In wsSheet3.Range(wsSheet3.Range("A2"), wsSheet3.Range("A" & Rows.Count).End(xlUp))
        Counted = 1
        y = wsSheet3.Range("D" & Rows.Count).End(xlUp).Row
        For i = 2 To x Step 1
            If c.Value <> wsSheet3.Range("A" & i).Value And Counted <= Number / 2 Then
                wsSheet3.Range("D" & y + Counted).Value = c.Value
                wsSheet3.Range("E" & y + Counted).Value = wsSheet3.Range("A" & i).Value
                Counted = Counted + 1
            ElseIf c.Value <> wsSheet3.Range("A" & i).Value And Counted <= Number Then
                wsSheet3.Range("E" & y + Counted).Value = c.Value
                wsSheet3.Range("D" & y + Counted).Value = wsSheet3.Range("A" & i).Value
                Counted = Counted + 1
            End If
        Next i
    Next
End Sub
===========================

I need to convert that to Javacript that can be fired off in Google Sheets. Can someone help?

Thanks in advance.
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'm trying simplify my example quite a bit here, any help would be greatly appreciated. So lets suppose I have 3 columns by 7 rows. Column A (A1) is named Plan Group, Column B (B1) called Transaction ID, and Column C (C1) called Total Amount Paid. The rows contain the data for all different Plan Groups (from A2:C6). Lets say the Plan Groups are called Microsoft (A2 & A7), Apple (A3), Netflix (A4), Home Depot (A5), At Home (A6).

My goal is to:

1.) Write a macro that will look for a specific Plan Group like Apple, and bring the data over to a new workbook, then save the new workbook as an xlsx based on the name of the Plan Group cell (A2 & A7).

Obviously I have around 145 different Plan Groups in real life (a lot with multiple rows but separate transaction ID's) in which will change a little monthly.
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

Microsoft Excel

131K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.