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
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
I fully agree with guys who told you that none will write the code for you, but you are lucky that I have that already written and there may be other people looking for same info, so here it is. Put the code to module in your master workbook and replace some data as commented: … Read more
The following might help you. Assuming you have a single contiguous range of cells, some of which contain a 4 character code, you can do Sub test() Dim r As Range Set r = Range(“A1:A6”) For Each c In r.Cells If Len(c) = 4 Then Debug.Print “cell “, c.Address(), ” had four characters: “, c.Value() … Read more
You can accomplish this with a VLOOKUP between two books. The guts of the equation are below. You wont actually type in the ‘[book2]SheetName’ portion, this will need to reflect the name of your other workbook and the sheet that you are looking at within that book. A2 = Look up value (in your example … Read more
Create a list of all the vendors in a separate place and remove duplicates. Then use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here’s the code for this, assuming the data you provided above is put in the cells … Read more
Try this: Inside the excel application go to the developer tab on the ribbon and click record macro. Type the values into the two cells manually. Click stop recording and view the macro it created. This would show you VBA code that will work and you can compare it to what you have in order … Read more
How to create a combination generator where order does not matter but being limited from a specific range of Sum? Using Excel VBA macro [closed] solved How to create a combination generator where order does not matter but being limited from a specific range of Sum? Using Excel VBA macro [closed]
To get some help, you need to specify where you get the error. This time you’ve been lucky, I’ve recognized an error I went through some time ago. Cause of the error With Application.FileSearch Error description Run-time error 445: Object doesn’t support this option Cause The method has been removed since > Excel 2003. In … Read more