none
Selecting and Editing Multiple Cells RRS feed

  • Question

  • Good morning, all -

    I have an Excel sheet from an outside vendor that I use as a source for SSIS processing. However, the column headers are different from what my SSIS routine wants to see. I know I could set up SSIS to ensure the correct headers are used, but the Excel source data may not be consistent; i.e., they may not use the same text for the columns all the time.

    So, I have an Excel Macro set up that sets the first cell for each column to the proper text for SSIS processing. Like so;

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Place Of Service"

    I use one code block like the above for each column in the sheet - A1:AU1.

    But, thought I, there must be a better way, perhaps some sort of loop. So, upon having that thought, I got on here to ask what the experts thought.

    Thanx for any assistance!

    Monday, June 3, 2019 1:24 PM

Answers

  • Adam,
    Re:  column titles

    Sub PlaceNamesAtTop()
      Dim vNames As Variant

      vNames = Array("Mud", "Midol", "Misery", "Mistake", "Meddlesome")
      Worksheets(1).Range("A1:E1").Value = vNames
    End Sub
    '---

    Adjust worksheet number or use the actual sheet name (enclosed in quotes).
    Adjust Range to agree with quantity of names.


    '---
    Custom_Functions add-in (19 new functions)
    Download from MediaFire...

    Tuesday, June 4, 2019 1:30 AM

All replies

  • Adam,
    Re:  column titles

    Sub PlaceNamesAtTop()
      Dim vNames As Variant

      vNames = Array("Mud", "Midol", "Misery", "Mistake", "Meddlesome")
      Worksheets(1).Range("A1:E1").Value = vNames
    End Sub
    '---

    Adjust worksheet number or use the actual sheet name (enclosed in quotes).
    Adjust Range to agree with quantity of names.


    '---
    Custom_Functions add-in (19 new functions)
    Download from MediaFire...

    Tuesday, June 4, 2019 1:30 AM
  • Adam,
    Re:  column titles

    Sub PlaceNamesAtTop()
      Dim vNames As Variant

      vNames = Array("Mud", "Midol", "Misery", "Mistake", "Meddlesome")
      Worksheets(1).Range("A1:E1").Value = vNames
    End Sub
    '---

    Adjust worksheet number or use the actual sheet name (enclosed in quotes).
    Adjust Range to agree with quantity of names.

    Aha! I knew there had to be a way. I figured an array would be involved, but I wasn't sure how to implement it.

    Thanx, NLtL!

    Tuesday, June 4, 2019 12:26 PM