Running Worksheet_Change on top of itself intentionally

See also questions close to this topic

• Dates in excel are appearing as a single number in Python

  For example the date in an Excel cell will read: "11/16/2018 9:20" , however in Python when I print the cell value of the date I get: "43420.38888". I have tried changing the date format in excel but Python reads it the same regardless. How do I get Python to see the date as a string or bytes-like object?
• Excel/Python excel latitude/longitude formatting

  I am currently working with the xlrd module in Python to work with longitude and latitude data. When I import the data to excel it comes in the format: 12:34'56.7"N , however I simply want it as: 1234N. Same idea for the longitude. I am new to programming so I don't know how to make this change, preferably as a line in Python. Thank you.
• Trying to open a new file and copy contents to that file if certain criterion is met. Not sure why the code is not giving any output

  Sub Importfile()
  
  On Error GoTo ErrHandler
      Application.ScreenUpdating = False
  
      Dim src As Workbook
      Dim nw As Workbook
      Set nw = Workbooks.Add
      Dim Rng As Range
      Set Rng = Range("B1")
  
      Set src = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xlsx),*.xlsx", _
             Title:="Select the EMAIL ID File")
  
      If src.Worksheets("Sheet1").Range("B1") <> "" Then
  
                  Rng.Copy Destination:=src.Range("B1")
      End if
  
  ErrHandler:
      Application.EnableEvents = True
      Application.ScreenUpdating = True
  
  End Sub
  
• Excel/VBA converts dates to American format on transpose/paste from VBA

  I'm working in a British context right now. Which I think I have set up in regional settings (Windows 7 in a corporate context, Excel 2016, ver 1803 (I think this may also be Office 365-? Whatever that is.)). I'm trying to work with dates in VBA and paste the results to Excel. At the beginning, and at the end, the dates are treated in UK format. But in the middle Excel insists on converting them to American. Pasting does: UK-> US -> UK.

  Dim p_dtTermArray() As Date
  ReDim p_dtTermArray(1 To lgNumTerms)
  p_dtTermArray(1) = DateSerial(2018, 10, 1) 
  p_dtTermArray(2) = DateSerial(2018, 11, 1) 
  

  Let's look in the Locals window:

  p_dtTermArray(1)        #1/10/2018#       Date
  p_dtTermArray(2)        #1/11/2018#       Date
  

  Before any pasting, I've cleared the worksheet, both manually with Ctrl-A-A, Alt-E-A-A, and in code with ...UsedRange.Clear.

  Well the situation just evolved as I'm writing this and trying things.

  If I paste this way:

  rgOutput.Offset(0, 0) = p_dtTermArray(1)
  rgOutput.Offset(0, 1) = p_dtTermArray(2)
  

  I get Excel values of "1/10/2018" and "1/11/2018", with a difference of 31 days. Which is right.

  But if I do this (a column array, with Transpose):

  wb.Range(rgOutput.Offset(3, 6), rgOutput.Offset(2 + lgNumTerms, 6)) = Application.Transpose(p_dtTermArray)
  

  The first two values are "10/01/2018" and "11/01/2018" (which subtract to 1). So Oct and Nov have turned into Jan and Jan. Which is wrong. Let's throw this one in too (a row array, no Transpose):

  wb.Range(rgOutput.Offset(3, 7), rgOutput.Offset(3, 6 + lgNumTerms)) = p_dtTermArray
  

  Ah! Now they come in as longs!: 43374, 43405 (which are 31 apart again)!

  So it's (partly) due to Transpose! Let's try:

  Dim v1, v2, v3, v4, v5
  v1 = p_dtTermArray
  v2 = Application.Transpose(p_dtTermArray)
  v3 = Application.Transpose(v1)
  v4 = Application.Transpose(v2)
  v5 = Application.Transpose(Application.Transpose(v1))
  

  In v2 to v5, the Date type gets turned into String type. And all the strings are correct ("1/10/2018", "1/11/2018", for Oct and Nov).

  So for some reason, Excel/VBA use the wrong regional setting on these strings. If I type "1/10/2018" and "1/11/2018" in two cells, and subtract them, I get 31. But when my transposed array puts those same two strings into two cells, they appear as "10/01/2018" and "11/01/2018" (and subtract to 1). The pasted strings are read as American dates and then converted to UK.

  I don't know where else to look for what might govern the misreading of these strings in one very specific context. Is the right thing just to avoid Transpose with dates (and, what?, manually transpose for column vectors (when you need to have the flexibility to do either row or column)?)?

  (In all the examples above, the dates in the worksheet have General format. I have tried using date formats on the cells both before and after pasting, with no improvement. In a different time and place when I did similar work, my practice was to avoid using dates in Excel cells, and hide the fact that my strings were meant to be dates (making strings of form MMM.YY), to avoid having Excel alter them. This time I thought it might be nice to just use darned dates in darned Excel.)
• Sum, concatenate and merge data in several columns

  Need VBA macro to merge data in 4 columns. Tried to consolidate but it won't work.

  Please see the picture for a better understanding. The red arrow explains what I need.

  I need a working VBA to go from the 4 columns on the left to the 4 columns on the right: Combine data (all the rows from row #2 down) by column a & c while consolidating values in column b and concatenating values in column d.

  This code below is not working and is missing parts.

  Sub CombineRows()
  'This section combines and sum A and B but not C (1 to 6) and D and 
  'deletes rows that should not delete instead, because of the second 
  'part of the code
    Dim Rng As Range
    Dim InputRng As Range
    Dim nRng As Range
     Set InputRng = Application.Selection
     Set InputRng = Application.InputBox("Range :", xTitleId, 
       InputRng.Address, Type:=8)
     Set InputRng = InputRng.Parent.Range(InputRng.Columns(1).Address)
       With CreateObject("scripting.dictionary")
       .CompareMode = vbTextCompare
     For Each Rng In InputRng
  If Not .Exists(Rng.Value) Then
  .Add Rng.Value, Rng.Offset(, 1)
     Else
  .Item(Rng.Value).Value = .Item(Rng.Value).Value + Rng.Offset(, 1)
      If nRng Is Nothing Then
          Set nRng = Rng
      Else
          Set nRng = Union(nRng, Rng)
      End If
  Next
      If Not nRng Is Nothing Then
      nRng.EntireRow.Delete
  Next
  
  'Second Part To combine A and D but it's not combining (maybe because 
  'of the large amount of data and I also need the comma between values 
  'in column D, not space but it doesn't work - deletes data
    Dim WorkRng As Range
    Dim Dic As Variant
    Dim arr As Variant
      On Error Resume Next
      xTitleId = "Combine"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, 
        WorkRng.Address, Type:=8)
      Set Dic = CreateObject("Scripting.Dictionary")
        arr = WorkRng.Value   
      For i = 1 To UBound(arr, 1)
        xvalue = arr(i, 1)
      If Dic.Exists(xvalue) Then
        Dic(arr(i, 1)) = Dic(arr(i, 1)) & " " & arr(i, 2)
      Else
        Dic(arr(i, 1)) = arr(i, 2)
      End If
   Next
      Application.ScreenUpdating = False
        WorkRng.ClearContents
        WorkRng.Range("A1").Resize(Dic.Count, 1) = 
      Application.WorksheetFunction.Transpose(Dic.keys)
        WorkRng.Range("D1").Resize(Dic.Count, 1) = 
      Application.WorksheetFunction.Transpose(Dic.items)
      Application.ScreenUpdating = True
    End Sub
  

  The "Set InputRng = Application.Selection and Set InputRng = Application.InputBox" can be deleted as the range is always the same.

  Image Click Here