• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Copying certain info from an excel cell to another cell

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
zoeg
Asked:
zoeg
  • 4
  • 4
1 Solution
 
eosuCommented:
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
 
zoegAuthor Commented:
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
 
keenezCommented:
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.

 
eosuCommented:
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
 
eosuCommented:
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
 
zoegAuthor Commented:
When I try this it doesn't do any thing :-( What am I doing wrong?
0
 
eosuCommented:
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
 
zoegAuthor Commented:
No row 1 in col H doesn't have anything init. The first one with a value is row 7.
0
 
zoegAuthor Commented:
Brill! Thanks work great now I've got rid of the blank cells in Col H!

Thanx again!

Zoƫ
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now