?
Solved

Copying certain info from an excel cell to another cell

Posted on 2003-03-11
9
Medium Priority
?
186 Views
Last Modified: 2010-04-07
This is an excel spreadsheet that I am (trying) to write a macro to take certain information (UG# 123456789 and TPS) out of cells in column H.

This is the part of my code that I need help with;


    Dim i As Integer
    Dim TheValue As String

    MaxRow = 1000

    For i = 1 To MaxRow
        TheValue = Left(Range("H" & CStr(i)).Text, 13)
        Range("J" & CStr(i)).Value = TheValue
    Next i

 - Here I want to search the same cells (column H) and find "TPS" which is not always in the same place and copy "TPS" in to the cell in column K of that row.
   
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],""-"",RC[2])"
    Columns("I:I").Select
    Range("I45").Activate
    Columns("I:I").EntireColumn.AutoFit
   
    Columns("J:K").Select
    Range("J45").Activate
    Selection.Delete Shift:=xlToLeft

Sample Data;

UG#: 99085417, Activity:  ENCL UG , Phase: 3G, Site Type: TPS, Cabin: , PW#: Scope: I&C&I, Additional Scope: .   BTS Build: icee,,JA,, Commission: icee,,,FM,,  Power Team: icee,,GS,, Delivery Team: ,,,.

UG#: 99085417, Activity:  ENCL UG , Phase: 3G, Site Type: TPS, Cabin: , PW#: Scope: I&C&I, Additional Scope: .   BTS Build: icee,,JA,, Commission: icee,,,FM,,  Power Team: icee,,GS,, Delivery Team: ,,,.

TPS UG#: , Activity:  NEW CELL , Phase: 3G, Site Type: Cabin: 2H, PW#: Scope: INSTALL, Additional Scope: .   BTS Build: EC,mb,,, Commission: ,,,,,  Power Team: EC,mb,,, Delivery Team: ,,,.

UG#: , Activity:  NEW CELL , Phase: 3G, Site Type: Cabin: , PW#: Scope: INSTALL, Additional Scope: Special Transport to Site.   BTS Build: btl,AB/KN,,, Commission: ,,,,,  Power Team: btl,AW,,, Delivery Team: ,,,. TPS

Any help ASAP would be great!
0
Comment
Question by:zoeg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:eosu
ID: 8110147
1. Does all the sample data belong in one cell per row or is it delimited?

2. If the column contains the letters TPS then you want them replicated in column K??  If it doesn't contain TPS then col K just holds the UG#???

Basically you need to list what data you want to see in col K after the macro runs.

Will have this done 5 mins after you post the answers to above question.

eosu
0
 

Author Comment

by:zoeg
ID: 8110365
Sample data

One cell
UG#: 99085417, Activity:  ENCL UG , Phase: 3G, Site Type: TPS, Cabin: , PW#: Scope: I&C&I, Additional Scope: .   BTS Build: icee,,JA,, Commission: icee,,,FM,,  Power Team: icee,,GS,, Delivery Team: ,,,.

One cell
UG#: 99085417, Activity:  ENCL UG , Phase: 3G, Site Type: TPS, Cabin: , PW#: Scope: I&C&I, Additional Scope: .   BTS Build: icee,,JA,, Commission: icee,,,FM,,  Power Team: icee,,GS,, Delivery Team: ,,,.

Column k

DJW/03/AD/27. UG#: 99235338 TPS

Or

UG#: 99235338 TPS

Or

UG#: 99235338

Copy first 12 + UG#??? + TPS

Thanks
0
 
LVL 2

Expert Comment

by:keenez
ID: 8112172
You could use the function instr() to see if "TPS" is within the string (value) of the cell.  

One pitfall though is if the characters TPS are found but in a different context than you were expecting (I can't think of a word with tps in it right now but there exists the possibility).

Perhaps looking for "Site Type: TPS".  Instr will return 0 if it's NOT found and the starting location of the string if it is found.

Cheers,

Keenez
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:eosu
ID: 8112360
Should have said 5 mins after I get the comment...

Anyway this should do it.

Sub FillK()
    Dim i As Integer, j As Integer, iStart As Integer, iEnd As Integer
    Dim strValue As String
   
    ' find the UG# number.
    i = 1
    With Application.ActiveSheet
   
        While .Cells(i, 8).Value <> ""  ' col 8 = H
            iStart = InStr(1, .Cells(i, 8).Value, "UG#:")
            If iStart > 0 Then
                iEnd = InStr(iStart, .Cells(i, 8).Value, ",") ' read to the comma as some rows have no number
                strValue = Mid(.Cells(i, 8).Value, iStart, iEnd - iStart) ' includes UG#: portion
               
                .Cells(i, 11).Value = strValue ' put UG# number in col K
            End If
           
            ' now for the TPS
            iStart = InStr(1, .Cells(i, 8).Value, "Site Type:")
            If iStart > 0 Then
                iEnd = InStr(iStart, .Cells(i, 8).Value, ",") ' Read to the comma after Site Type
                strValue = Mid(.Cells(i, 8).Value, iStart + 11, iEnd - iStart - 11) ' 11 will exclude the Site Type: string
               
                If strValue = "TPS" Then
                    .Cells(i, 11).Value = .Cells(i, 11).Value & " " & strValue
                End If
            End If
           
            i = i + 1
        Wend
    End With
           
       
       
End Sub
0
 
LVL 1

Accepted Solution

by:
eosu earned 2000 total points
ID: 8112377
One of the rows had TPS in it but was not in the Site Type field, so not sure what you wanted to do there.  Anyway here is the version that just includes TPS if it exists in the string at all...

Sub FillK()
    Dim i As Integer, j As Integer, iStart As Integer, iEnd As Integer
    Dim strValue As String
   
    ' find the UG# number.
    i = 1
    With Application.ActiveSheet
   
        While .Cells(i, 8).Value <> ""  ' col 8 = H
            iStart = InStr(1, .Cells(i, 8).Value, "UG#:")
            If iStart > 0 Then
                iEnd = InStr(iStart, .Cells(i, 8).Value, ",") ' read to the comma as some rows have no number
                strValue = Mid(.Cells(i, 8).Value, iStart, iEnd - iStart) ' includes UG#: portion
               
                .Cells(i, 11).Value = strValue ' put UG# number in col K
            End If
           
            ' now for the TPS
            iStart = InStr(1, .Cells(i, 8).Value, "TPS")
            If iStart > 0 Then
                iEnd = iStart + 3
                strValue = Mid(.Cells(i, 8).Value, iStart, iEnd - iStart)
               
                .Cells(i, 11).Value = .Cells(i, 11).Value & " " & strValue
            End If
           
            i = i + 1
        Wend
    End With
           
       
       
End Sub
0
 

Author Comment

by:zoeg
ID: 8174550
When I try this it doesn't do any thing :-( What am I doing wrong?
0
 
LVL 1

Expert Comment

by:eosu
ID: 8174917
1. Check that you can run macros - Tools, Macros, Security - set to medium.  this will make Excel warn of macros when opening the file. Make sure you enable macros when asked upon opening.
2. Are you sure you are running the macro? It doesn't run automatically.
3. Is there a value in row 1, col H.  The first row the macro comes to that does not have a value in col H will cause the macro to stop.

let me know how you get on.
0
 

Author Comment

by:zoeg
ID: 8176733
No row 1 in col H doesn't have anything init. The first one with a value is row 7.
0
 

Author Comment

by:zoeg
ID: 8180064
Brill! Thanks work great now I've got rid of the blank cells in Col H!

Thanx again!

Zoë
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question