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

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

Select all 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

NEED TO DELETE ROWS A-O in every csv in a directory , need a vba code to do this

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.

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.

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) 

Select all Open in new window

…

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

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.

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.

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

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.

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.

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.