Excel VBA Help

[mlr0911]

Hello, I am hoping that someone can help me with a VBA solution to my problem. I am trying to create a script that would loop through an excel spreadsheet and fill in the information needed.

What I am trying to accomplish is this.

For every cell that has a length of 9 remember the first 6 characters and if the next cell has a length of 3, paste the remembered characters at the beginning of the string and move on to the next cell. I have pasted an example.

Cell Format Desired Format
123456ABC 123456ABC
DEF 123456DEF
GHI 123456GHI
7890123TR 7890123TR
FVD 7890123FVD
DCW 7890123DCW
EDC 7890123EDC
WQA 7890123WQA
QWS 7890123QWS


Any suggestions?
Thanks in advance for your help.

[jetted]

Hi

Would this help?

Please Login or Register  to view this content.

[antoka05]

If you have data in column 'A' from row 1 you can use this code:

   lastRow = Range("a65536").End(xlUp).Row

   textToRemember = ""
   For r = 1 To lastRow
      myText = Cells(r, 1).Value
      If Len(myText) = 9 Then
         textToRemember = Left(myText, 6)
         Cells(r, 2) = myText
      Else
         Cells(r, 2) = textToRemember & myText
      End If
   Next

Regards,
Antonio

[antoka05]

I excuse for the format of previous code:

lastRow = Range("a65536").End(xlUp).Row

textToRemember = ""
For r = 1 To lastRow
myText = Cells(r, 1).Value
If Len(myText) = 9 Then
textToRemember = Left(myText, 6)
Cells(r, 2) = myText
Else
Cells(r, 2) = textToRemember & myText
End If
Next

[mlr0911]

Thanks you guys!!!! Both worked. You Rock!