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.
Insert this line to your code Range(“B3:B100”).Copy Destination:=Range(“C3:C100”) The range destination can be changed depending on which range you want to paste the data.
Try the Application.Getopenfilename This opens the dialogue for picking files. If you want to dispaly a specific folder, use this code before the Getopenfilename: chdir “c:\whatever\wherever” Are you sure you need a separate instance of Excel? It has many disadvantages, and only a few advantages.
@David Zemens idea.. this should work .Tested Sub test() Dim str As String Dim s() As String str = “15f,158,1669,187,15,156,47t” s = Split(str, “,”) For i = LBound(s) To UBound(s) If Len(s(i)) = 3 Then t = IIf(t = “”, s(i), t & “,” & s(i)) End If Next i MsgBox (t) End Sub
As per comments I’ve amended the code to copy only the specified range, both Sheets should exist, the code will not create the second Sheet for you: Sub Test() Dim Cell As Range With Sheets(“Sheet1”) ‘Sheet with data to check for value ‘ loop column H untill last cell with value (not entire column) For … Read more
I’ve got an answer that will find the first instance of USD and check if TOTAL PURCHASE is 9 rows below it, if found then copy the adjacent cell to D1, if not found then search for the next USD and repeat the checks: Option Explicit Sub vbavjezba() Dim total As Variant Dim usd As … Read more
I tried to always use an autofilter to find the empty cells before removing them but I’m stuck with it . instead i used SpecialCells function Since i want to delete the Empty cells. So it Works but if I want to delete other celles dipending other special criteria than blank or somting that is … Read more
Why would you want to do this? VBA doesn’t have a keyword that represents entering into a cell. You can select the cell like this though: Sub DoNotRun() Dim rng as range For Each rng In ThisWorkbook.Worksheets(“Sheet1”).range(“A:A”) rng.Select Next rng End Sub If you really need to simulate a double click — Worksheets have a … Read more
thanks for sharing your code. I think it looks a bit more than what you need. Going off your example, if everything is formatted as such, this would be your solution: Option Explicit Sub SplitDateTime() Dim mydate As String, mytime As String, mytime2 As String, i As Long, sht As Worksheet, lastrow As Long Set … Read more
Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Range(“A” & Target.Row) = Range(“B” & Target.Row) Then MsgBox “Buy – ” & Range(“E” & Target.Row) ElseIf Range(“A” & Target.Row) = Range(“C” & Target.Row) Then MsgBox “Sell – ” & Range(“E” & Target.Row) End If End Sub The key is using the Worksheet_Change event in the … Read more