You could use Dictionary object: Sub test() Dim keywordColumn As String, productColumn As String Dim products As String Dim i As Integer Dim myKey, p ‘after adding reference to Microsoft Scripting Runtime ‘Dim Keywords As New Dictionary Dim Keywords As Object Set Keywords = CreateObject(“Scripting.Dictionary”) keywordColumn = “B” productColumn = “A” With ActiveSheet maxRow = … Read more
Usage of GetObject() is documented in this old KB article. Error handling is required to get the first instance created. Like this: Dim excel On Error Resume Next Set excel = GetObject(, “Excel.Application”) If Err.number = 429 Then Set excel = CreateObject(“Excel.Application”) End If If Err.number <> 0 Then WScript.Echo “Could not start Excel: ” … Read more
I think this should pretty much do what you want. Sub Extract() On Error Resume Next Set myOlApp = Outlook.Application Set mynamespace = myOlApp.GetNamespace(“mapi”) Set myfolder = myOlApp.ActiveExplorer.CurrentFolder Set xlobj = CreateObject(“excel.application.14”) xlobj.Visible = True xlobj.Workbooks.Add xlobj.Worksheets(“Sheet1”).Name = “Statusmail” ‘Set the header xlobj.Range(“a” & 1).Value = “Absender” xlobj.Range(“a” & 1).Font.Bold = “True” xlobj.Range(“b” & 1).Value … Read more
Here is an example that does this. Make sure you run it with the sheet you want to use up: Sub DeleteDupes() Dim x For x = Cells(Rows.CountLarge, “D”).End(xlUp).Row To 1 Step -1 If Cells(x, “D”) = Cells(x, “E”) Then ‘This line deletes the row: Cells(x, “D”).EntireRow.Delete xlShiftUp ‘This line highlights the row to show … Read more
Maybe something like this Sub DeleteRows() Dim c As Range Dim SrchRng As Range Dim SrchStr As String Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets Set SrchRng = sh.Range(“b1”, sh.Range(“b65536”).End(xlUp)) SrchStr = sh.Range(“k1”) Do Set c = SrchRng.Find(SrchStr, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete Loop While Not c Is Nothing Next … Read more
Based on what you’ve told me, this is what I can provide. Running it will find the first occurrence where Column A is not blank and Column B is missing text. You can then have your user fill it in, run it again to check for more. Sub checkcolumns() Dim i As Long ‘100 is … Read more
A solution with a formula only: We assume that A1=1-3. Fill the following formula into A2 and copy it down. =IF(A1<>””,IF(ISNUMBER(A1),IF(A1+1<=VALUE(RIGHT(A$1,LEN(A$1)-FIND(“-“,A$1))),A1+1,””),VALUE(LEFT(A$1,FIND(“-“,A$1)-1))),””) The result will be 1 2 3 This works for any numbers devided by -. 1 solved Excel Number Separation [closed]
In my testing this does exactly what you asked for. You will need to rename the Sheets depending on what your sheet names for the original data sheet name is and your output / result sheet name is. Option Explicit Sub splittinghours() Dim DataSheet As Worksheet Dim ResultSheet As Worksheet Set DataSheet = ThisWorkbook.Sheets(“Sheet1”) Set … Read more
Because You hide Columns(“C:NI”) and unhide only 30 columns from column C to column AF. Range(“B3”).Value equals to 1 and Range(“B3”).Value * 31 – 29 equals to 2. Likewise, Range(“B3”).Value * 31 + 1 equals to 32. So, you unhide only 30 columns (32-2)! Just change your VBA code Range(“B3”).Value * 31 – 29 to … Read more
Use the sheets property of the workbook to return a collection that you can loop through with For Each. Skip over the Names sheet and avoid reading the names sheet multiple times by putting the values into an array. Option Explicit Sub multiFindandReplace() Dim wb As Workbook, ws As Worksheet Dim myList As Variant, myRange … Read more
Declare the items as Object. You also have your For Each loop out of order. Function GetCPUID() As String Dim cimv2 As Object Dim PInfo As Object Dim PItem As Object Dim PubStrComputer As String PubStrComputer = “.” Set cimv2 = GetObject(“winmgmts:\\” & PubStrComputer & “\root\cimv2”) Set PInfo = cimv2.ExecQuery(“Select * From Win32_Processor”) For Each … Read more
For Column A (excluding first row for header)… Option Explicit Sub Delete_3_4() Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(“Sheet1”) Dim SearchRange As Range, SearchCell As Range, DeleteMe As Range Set SearchRange = ws.Range(“A2:A” & ws.Range(“A” & ws.Rows.Count).End(xlUp).Row) For Each SearchCell In SearchRange If Left(SearchCell, 1) = 3 Or Left(SearchCell, 1) = 4 Then If … Read more
Here is one of the solutions: Prerequisites: Sheet1 contains original data (track# in column A, data to split in column B and comment/date in column C), Sheet2 will contain processed data. Hope that helps. The code (click Alt+F11, click Insert/Module, paste the code in the inserted module): Sub test() Dim a As String, g As … Read more
Edit: The code below should now arrange the totals by date. Private Sub Total() Dim apple As Integer Dim banana As Integer Dim grape As Integer Dim pear As Integer Dim lemon As Integer Dim orange As Integer Dim sheet As Worksheet Dim i As Integer Dim lastRow As Integer Dim j As Integer Dim … Read more
If you are Sorting columns A to H you could use the below Columns(“A:H”).Select ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“B:B”), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“H:H”), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(“Sheet1”).Sort .SetRange Range(“A:H”) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With All you needed to do was record the macro … Read more