vba

[Solved] excel multiple column matches [closed]

by

This code will do what you are intending with the information given. I encourage you to look up the methods used as they are quite fundamental and novice in difficulty. I have put down comments that explain what each section is doing. GL Sub test() Dim filter, C1, C2 As String Dim counter As Integer … Read more

[Solved] Excel VBA sort bug

by

Key:=source.Range(source.Cells(rowStart, startNames)… – You shouldn’t have source.Range here – your key is the single cell source.Cells(rowStart, startNames). As a recommendation – change …SortFields.Add2 to …SortFields.Add. The ..Add2 will definitely not work in older versions of Excel. solved Excel VBA sort bug

[Solved] Excel macro to take a screenshot of specific cells and save as image file

by

This appears to work: Sub Macro1() myFileName = “chrt.png” Range(“G3:J14”).Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Charts.Add ActiveChart.Paste ActiveChart.Export Filename:=ThisWorkbook.Path & “\” & myFileName, Filtername:=”PNG” End Sub But you may need to resize or edit the resulting picture to meet your needs. You also may want to delete the Chart when you are done. 4 solved Excel macro … Read more

[Solved] Extract Last Bracketed Characters from String in Excel VBA

by

Try this UDF Function ExtractByRegex(sTxt As String) With CreateObject(“VBScript.RegExp”) .Pattern = “V\d+(.)?(\d+)?” If .Test(sTxt) Then ExtractByRegex = .Execute(sTxt)(0).Value End With End Function Update Here’s another version in which you can format the output Sub Test_ExtractByRegex_UDF() MsgBox ExtractByRegex(“A9 V2.3 8.99”) End Sub Function ExtractByRegex(sTxt As String) With CreateObject(“VBScript.RegExp”) .Pattern = “V\d+(.)?(\d+)?” If .Test(sTxt) Then sTxt = … Read more

[Solved] Executing several actions within Before_Close

by

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sh As Worksheet, lastRow As Long, lastCol As Long, emptyCells As Range Set sh = ActiveSheet lastRow = sh.Range(“A” & Rows.Count).End(xlUp).Row lastCol = sh.Cells(4, Columns.Count).End(xlToLeft).Column On Error GoTo NoBlanks Set emptyCells = sh.Range(sh.Cells(4, 1), sh.Cells(lastRow, lastCol)). _ SpecialCells(xlCellTypeBlanks) If Not emptyCells Is Nothing Then MsgBox “There are empty cells, … 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] Word VBA force save as .docm

by

In order to hijack the native “SaveAs” dialog in Word, you need to lever the Application-level event for DocumentBeforeSave, and then call the FileDialog manually, in order to validate the extension. 1. Create a standard code module and name it modEventHandler. Put the following code in it. Option Explicit Public TrapFlag As Boolean Public cWordObject … 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] Run R function in VBA macro

by

You can run an R script in VBA by creating Windows Shell obejct and passing it a string that executes an R script Sub RunRscript() ‘runs an external R code through Shell ‘The location of the RScript is ‘C:\R_code’ ‘The script name is ‘hello.R’ Dim shell As Object Set shell = VBA.CreateObject(“WScript.Shell”) Dim waitTillComplete As … Read more