[ad_1] First of all you need to loop through all your worksheets for both, open the 2 workbooks, and loop through both, then you can select them, compare if their name exists in the other worksheet, if not, you add it, otherwise, you copy the content from one to the other one and at the … Read more
[ad_1] I got this question answered here by user Subodh Tiwari (Neeraj): https://www.experts-exchange.com/questions/29098511/VBA-VLOOKUP-With-Multiple-Parameters.html#acceptAnswerByMember Sample workbook is attached with the post in this link. Here is the complete code: Sub PlaceFormula() Dim ws As Worksheet Dim lr As Long Dim lc As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With … Read more
[ad_1] VBA or Visual Basic for Applications is a small scripting language which can be used in Microsoft applications to automate tasks, extend functionality and more. Each host application e.g. Word, Excel provides a set of APIs to VBA which the programmer can make use of to interact with said host application and the open … Read more
[ad_1] I solved this by recording a macro that copys and pastes the data values into another section then I sorted it largest to smallest. I then created a column with the equation to subtract one from the other. [ad_2] solved Excel VBA, code to count the # of filled cells, then find highest and … Read more
[ad_1] The issue is that openpyxl doesn’t evaluate the formula in excel. it will only return the last value saved by excel or ‘None’ (with data_only=True). The latter is what is happening when you change an input cell and use [cell].value to call the value of the cell with the formula. When you don’t change … Read more
[ad_1] Try something like if workbooks(“First_workbook address”).sheet1.range(“A1”).value= workbooks(“second_workbook address”).sheet1.range(“C1”).value then workbooks(“First_workbook address”).sheet1.range(“A1”).colour = vbRed Endif [ad_2] solved How to compare two Excel workbook, and if the data matches on both sheet, color code from second should automatically apply to first one [closed]
[ad_1] 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 … Read more
[ad_1] Method 1 HierarchicalItem holds the information we need about an item in the folder. An item can be a folder or file. class HierarchicalItem { public string Name; public int Deepth; public HierarchicalItem(string name, int deepth) { this.Name = name; this.Deepth = deepth; } } SearchDirectory is the recursive function to convert and flatten … Read more
[ad_1] 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. [ad_2] solved Excel VBA sort bug
[ad_1] 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 [ad_2] solved … Read more
[ad_1] You would not be able to do this only with excel formulas and you would need a VBA solution. If your Sheet1 contains data like below, and Sheet2, Try this simple VBA code , Sub uniqueList() Dim i As Long, j As Long, str As String For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row For … Read more
[ad_1] 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
[ad_1] EDIT#1: If the value ‘42248’ has somehow gotten into A1, then in another cell enter: =–TEXT(A1,”d.m”) to retrieve the 1.9 5 [ad_2] solved Regain an original fractional numeric value that Excel has transformed to a date value
[ad_1] 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 … Read more
[ad_1] Sorry for the length of this response, but there are a lot of steps involved and I included quite a bit of screen clips as well. I believe this solution does what you are looking for. I start with files in a folder: Feb-19.xlsx contains two tabs: Jan-19.xlsx contains three tabs: I open a … Read more