Solved Excel 2010: Cell contains specific text, insert that text...

August 10, 2015 at 08:58:42
Specs: Windows 64
I am making a database that imports data from excel files.

However, those files are not originally from excel. So, I have to pull data about once a week from a system that saves the files to a format that excel can read, but Access doesn't want to read because it is not in the xml format. I have to convert the data to excel (easy as opening the file in excel and saving it as an xml). I then go into the worksheet and have to change the first row, because it contains repeating data and they need to be unique fields of course.

The first row is used as the header information. The first 10 columns A1 -J1 are fine as the data is unique in each. K1 is where the issue begins. K1 contains a year and other text. The following 13 columns L1:X1 contain other text without the year from K1. This repeats at Y1 having a year and the following 13 columns not having a year but having the same text as L1:X1. I need to take the year from K1 (the year will be the first four characters) and insert that year as text into the next 13 columns (as the beginning text of the cell) so they become unique. This needs to happen about 20 times, 2015 - 2035.

This is tedious at best doing it by hand, but I have to do it for every piece of data I pull. I could do it by hand once and then copy and paste it into all of the new worksheets, however I'd prefer to have a macro I could run to do this.

Any help would be greatly appreciated!


See More: Excel 2010: Cell contains specific text, insert that text...

Report •


#1
August 10, 2015 at 09:29:14
Try this. The code is written for the K1, L1:X1 situation. Expand/modify it to extend across all of your other ranges. As written, it inserts the first 4 characters from K1 and a space at the beginning of L1:K1.

Sub AddYear()
'Loop through Range
    For Each myCell In Range("L1:X1")
'Insert first 4 characters from K1
      Range(myCell.Address) = Left(Range("K1"), 4) & " " & myCell
    Next
'Repeat code for other Ranges
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
August 12, 2015 at 14:16:27
So I'll have to make 21 of these modules to do from years 2015 to 2035, correct?

Report •

#3
August 12, 2015 at 18:55:17
✔ Best Answer
That depends. Do you always have the same number of cells between each year, e.g. 13?

If so, you can loop 21 times and increment the cell containing the Year by 14 each time through the loop:

Sub InsertYear()
'Initialize Year column variable for first pass
'Column K is Column 11
  yearCol = 11
'Loop 21 times (2015 - 2035)
   For nxtYear = 1 To 21
'Loop through the 13 cells following the Year cell
     For nxtCell = 1 To 13
'Insert first 4 characters from yearCol
        Cells(1, yearCol + nxtCell) = _
           Left(Cells(1, yearCol), 4) & " " & Cells(1, yearCol + nxtCell)
     Next
'Increment yearCol for next Column with a Year
        yearCol = yearCol + 14
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 13, 2015 at 03:55:06
You are correct, it's the same number of cells. The first 10 are unique with identifiable information. The next 14 are per year with the 1st being unique because it has the year and the following 13 headers repeating for every following year. This looks like it will look great! I'll try it this morning. Either way, thank you very much!

Report •

#5
August 13, 2015 at 08:05:34
That worked terrifically, with one problem. I realized that after the 7th loop, the 8th group changed and had one less field. However, I was able to use what you wrote and modify it to the following which works perfectly. THANK YOU!

Sub InsertYear()
'Initialize Year column variable for first pass
'Column K is Column 11
yearCol = 11
'Loop 7 times (2015 - 2022)
For nxtYear = 1 To 7
'Loop through the 13 cells following the Year cell
For nxtCell = 1 To 13
'Insert first 4 characters from yearCol
Cells(1, yearCol + nxtCell) = _
Left(Cells(1, yearCol), 4) & " " & Cells(1, yearCol + nxtCell)
Next
'Increment yearCol for next Column with a Year
yearCol = yearCol + 14
Next
'Loop 14 times (2023 - 2035)
For nxtYear = 1 To 14
'Loop through the 12 cells following the Year cell
For nxtCell = 1 To 12
'Insert first 4 characters from yearCol
Cells(1, yearCol + nxtCell) = _
Left(Cells(1, yearCol), 4) & " " & Cells(1, yearCol + nxtCell)
Next
'Increment yearCol for next Column with a Year
yearCol = yearCol + 13
Next
End Sub


Report •

#6
August 13, 2015 at 09:01:46
I'm glad it worked out for you.

If I may, in the future, please use the pre tags when posting code so that the indents are maintained as can be seen in my responses.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
August 17, 2015 at 08:17:41
Something like this?

Sub InsertYear()
'Initialize Year column variable for first pass
'Column K is Column 11
  yearCol = 11
'Loop 7 times (2015 - 2022)
   For nxtYear = 1 To 7
'Loop through the 13 cells following the Year cell
     For nxtCell = 1 To 13
'Insert first 4 characters from yearCol
        Cells(1, yearCol + nxtCell) = _
           Left(Cells(1, yearCol), 4) & " " & Cells(1, yearCol + nxtCell)
     Next
'Increment yearCol for next Column with a Year
        yearCol = yearCol + 14
    Next
'Loop 14 times (2023 - 2035)
   For nxtYear = 1 To 14
'Loop through the 12 cells following the Year cell
     For nxtCell = 1 To 12
'Insert first 4 characters from yearCol
        Cells(1, yearCol + nxtCell) = _
           Left(Cells(1, yearCol), 4) & " " & Cells(1, yearCol + nxtCell)
     Next
'Increment yearCol for next Column with a Year
        yearCol = yearCol + 13
    Next
End Sub


Report •


Ask Question