excel

[Solved] Read Excel file which has one of the column as Hyperlink through python

by

Pandas library does not have the functionality to parse hyperlink as of yet. You could preprocess the excel using libraries such as xlrd or openpxyl to get hyperlinks then use it in pandas. Source: https://stackoverflow.com/a/45446810/7570485 There is also a feature request for this functionality. https://github.com/pandas-dev/pandas/issues/13439 1 solved Read Excel file which has one of the … Read more

[Solved] Add lines and duplicate data a set number of times

by

Try this after renaming the referenced worksheet. Sub expandMonths() ‘https://stackoverflow.com/questions/52304181 Dim i As Long, j As Long, m As Long, a As Variant With Worksheets(“sheet1”) i = .Cells(.Rows.Count, “A”).End(xlUp).Row Do While i > 1 a = Array(.Cells(i, “A”).Value2, .Cells(i, “B”).Value2, 0, 0, 0, 0) m = .Cells(i, “C”).Value2 j = Application.Match(.Cells(i, “A”).Value2, .Columns(“A”), 0) If … Read more

[Solved] Deleting Duplicates EXCEL VBA Macro

by

This should work for you: Sub DeleteDuplicates() Dim lRow As Long Dim i, j, k As Integer Dim Duplicates() As Integer Dim sht As Worksheet Dim Val1, Val2 As String Set sht = Worksheets(“Sheet1”) lRow = sht.Cells(Rows.Count, 1).End(xlUp).Row Index = 0 For i = 7 To lRow Val1 = sht.Cells(i, 1).Value Index = 0 For … Read more

[Solved] How do I use vlookup in VBA to enter a value into that cell [closed]

by

Sub MyReplace() Dim rw As Long With ActiveSheet On Error Resume Next rw = Application.WorksheetFunction.Match(.Range(“B13”), .Range(“B4:B7”), 0) On Error GoTo 0 If rw > 0 Then .Range(“C” & rw + .Range(“B4:B7”).Row – 1).Value = .Range(“C13”) Else MsgBox “Name not found in range” End If End With End Sub 6 solved How do I use vlookup … Read more

[Solved] Automate IE print dialog box without using SendKeys [closed]

by

It is, by using the InternetExplorer object’s ExecWB method (see this link for details). After adding a reference to the Microsoft Internet Controls library to your project, the following example should get you started: Option Explicit Sub PrintWebPage() Dim ie As InternetExplorer Set ie = New InternetExplorer ie.Navigate “http://www.google.com/” ie.Visible = 1 ‘Wait for page … Read more

[Solved] Matching color of a cell with another cell using VBA [closed]

by

The following code copies the color value from cell “C11” to cell “C4”: Range(“C4”).Interior.Color = Range(“C11”).Interior.Color As long as you work with valid Range-Objects, you can use different types to address the cells as fits your needs. For example the active selection (at least one cell or more): Selection.Interior.Color = Range(“C11”).Interior.Color 1 solved Matching color … Read more

[Solved] Excel VBA to convert all Word files in a specific folder to PDF [closed]

by

I’ve finally found the correct VBA I was looking for: ‘In your VBA window go to tools then references and add a reference to ‘Microsoft Word Sub Converter() Dim cnt As Integer, currfile As String Dim TrimFile As String, Path As String, FilesInPath As String _ , MyFiles() As String, Fnum As Long Dim CalcMode … Read more

[Solved] VBA – copy different template sheets from a workbook, into multiple sheets of another workbook based on criteria on a summary excel sheet

by

Okay, so here’s some code to get you started. I based the names on the code you gave, which is why it was helpful. I’ve commented this a lot to try and aid your learning, there are only actually about a dozen lines of code! Note: this code will likely not work “as is”. Try … Read more