Paste from Excel to Word, font colour doesn't reflect

See also questions close to this topic

• 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
  
• 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
• How to write nested dictionary values to word document/text file in vba

  I'm having some difficulty with outputting dictionary values into a word document/text file. My code uses one scripting dictionary to store the document name and then a dictionary within each document that stores calculated values. Essentially, I have a loop where for i documents, values are obtained and stored. The values are stored properly and can be viewed when I debug.print, but I'm having trouble outputting the values to a document. The code in question is listed below, with Debug.Print lines commented out (this is where I'm hoping to output my dictionary values). I'm only showing the code relevant to the output of the stored dictionary values:

  Dim z As Scripting.Dictionary
  Dim z_sub As Scripting.Dictionary
  Dim v As Variant
  Dim v_sub As Variant
  Dim tot As Long
  Dim totTU As Long
  Dim totPercent As Long
  Dim outputDoc As Document
  
  Set outputDoc = Application.Documents.Add
  outputDoc.Activate
  For Each v In z.Keys
      Set z_sub = z(v)
      'Debug.Print v & ":" & vbCrLf
      For Each v_sub In z_sub
          'Debug.Print vbTab & v_sub & ":" & z_sub(v_sub) & vbCrLf
      Next
      tot = tot + z_sub("Full")
      totTU = totTU + z_sub("TU")
  Next
  totPercent = (totTU / tot) * 100
  Debug.Print vbCrLf & "Total word count: " & tot & vbCrLf
  Debug.Print "Total modified word count: " & totTU & vbCrLf
  Debug.Print "Total percentage: " & totPercent & "%"
  

  I'm open to using a txt file instead of a word document, but any advice would be welcome!

  Edit: Here is what the debug.print output looks like (and what I want it to look like in the final document):

  Alphanumeric_customer_compare1.docx:
  
      TUCount: 5547
      FullCount: 7897
      Percent: 70
  
  Alphanumeric_customer_compare2.docx: 
      TUCount: 5665
      FullCount: 7897
      Percent: 72
  
  Total word count: 15794
  Total TU word count: 11212
  Total percentage: 71%
  

  The last three variables (totals) are different from the dictionary values.
• 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.)