IF left(A32,3) = left(A33,3) then????? 2

mattloflin · Mar 6, 2008

Hey guys,

I have a spreadsheet that has two columns and those two columns have 8 columns within them, the Rows vary in number

Well in column one there is Column F and in column two there is column P the information varies in these columns from either 3 4 or 5 digits.

I need the following and I have tried different ways but i'm having "writers block"

If the total length of the cell - 1 (take off last letter) = total length of the next cell then add it to a <string>

This is for a continuity program.

So ex:

if 340a (340) = 340b (340) then add 340a and 340b to string

then it should go onto

if 340b (340) = 340c (340) add it to list

the list should then look like this:
340a
340b
340c

so all those are in column one I also need it to jump over to column two (the tricky part) and test the same "range" (this is where the attached file comes into play)

so say column two (range) has "340f" then it should add it.

I need this to run through the whole length to a lastrow

also it's be best if it added it to a string so that I could add variables and stuff so when I export all this info to a txt file i can make it look how i need it to look.

I will be here all day so if you have any questions let me know.

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

mattloflin · Mar 6, 2008

Here is another link.

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

ck1999 · Mar 6, 2008

A few questions

1. Will the number always be seperated by a empty row?

2. When you say Added to a string. Do you want this kept in code for later use in code or to be saved onto a sheet is so where?
3. If a string then your output would be 340a, 340b,340C? if so what do you want to be between a comma, colon, spaces?

ck1999

mattloflin · Mar 6, 2008

1.yes the number will always be seperated by an emtpy row on the 1st column the second one varies

2. see this is part two to another project. The other project we have worked on on here. The other program searches through another spreadsheet and saves stuff like this:

PROBEPOINTTEST,$J1-33,COLOR,BN/BU,LABEL,30F Sld Black ENG FMTRNG

"probe..." is point 1
$J1-33 is point 2
Color is just color
BN/Bu is the color Brown/Blue
Label is just label
30F is the label

so for instance with this spreadsheet:

it'd end up look like for the first one
"Probepointtest,$C,Color,PK,Label,Auxilary Power"

So if it was a string it could be like

STRING = "Probepointtest," & (cell) & ",Color," & (cell) & ",Label," & (cell)

savedstring = savedstring & vbcrlf & string

3. I think the previous stuff answers that question (i think)

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

mattloflin · Mar 6, 2008

also this will all be saving to a txt document (this is already programmed)

I have attached a copy of a completed txt document.

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

mattloflin · Mar 6, 2008

oh! i have a temporary spreadsheet opened during the other part of this.

I have attached the macro that i'm adding onto.

if we just wanted to add all this info after the new last row that'd be fine.

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

ck1999 · Mar 6, 2008

Can you post a spreadsheet with a sample of your desired output?

ck1999

mattloflin · Mar 6, 2008

I have found a different file that is being made. That might help us out A TON

(attached)

Column A would be the Label
Column B would be added to the Label
Column C would be the Pin (#J1-33) IF
it equaled:
PI
J1
J2
J3
HFV6
FPR
SIDI E
SIDI O
TCM
APP
API
Column G would be the Color

also if H I or J's backcolor does not equal white then i need it to add the Columns Name (2' Past etc) to the Label

does this make sense?

Pretty much we can scratch the old macro and all that stuff.

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

mattloflin · Mar 6, 2008

heres my current code

Code:

Sub GTS()
'
' GTS Macro
' Macro recorded 2/25/2008 by Matt Loflin
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
    
Dim NewName As String
Dim x As String

x = Application.ActiveWorkbook.FullName
oldx = Application.ActiveWorkbook.Name
    
    ' Rename Full Filename
    If Right((x), 8) = " GTS.xls" Then
        NewName = Left((x), Len(x) - 8) & " GTS" & ".txt"
    Else
        NewName = Left((x), Len(x) - 4) & " GTS" & ".txt"
    End If
    
    'Add New Workbook
    Workbooks.Add
    newx = Application.ActiveWorkbook.Name
    
    'Copy and Paste Columns
    Windows(oldx).Activate
    Columns("A:C").Copy
    Windows(newx).Activate
    Columns("A:C").Select
    ActiveSheet.Paste
    Windows(oldx).Activate
    Columns("G:J").Copy
    Windows(newx).Activate
    Columns("D:G").Select
    ActiveSheet.Paste
    
    Dim ocell As Range
    
    For i = newlastrow To 2 Step -1
        If Left(Range("c" & i), 2) <> "J1" And _
           Left(Range("c" & i), 2) <> "J2" And _
           Left(Range("c" & i), 2) <> "J3" And _
           Left(Range("c" & i), 2) <> "PI" And _
           Left(Range("c" & i), 4) <> "HFV6" And _
           Left(Range("c" & i), 3) <> "FPR" And _
           Left(Range("c" & i), 6) <> "SIDI E" And _
           Left(Range("c" & i), 6) <> "SIDI O" And _
           Left(Range("c" & i), 3) <> "TCM" And _
           Left(Range("c" & i), 3) <> "APP" And _
           Left(Range("c" & i), 3) <> "API" Then
               Range(i & ":" & i).EntireRow.Delete
        End If
        If Left(Range("c" & i), 2) = "J1" And _
           Left(Range("c" & i), 2) = "J2" And _
           Left(Range("c" & i), 2) = "J3" And _
           Left(Range("c" & i), 2) = "PI" And _
           Left(Range("c" & i), 4) = "HFV6" And _
           Left(Range("c" & i), 3) = "FPR" And _
           Left(Range("c" & i), 6) = "SIDI E" And _
           Left(Range("c" & i), 6) = "SIDI O" And _
           Left(Range("c" & i), 3) = "TCM" And _
           Left(Range("c" & i), 3) = "APP" And _
           Left(Range("c" & i), 3) = "API" Then
                Cells(i, 2).Value = "$" & Cells(i, 2).Value
        End If
    Next i
    
    Rows(1).Delete
    lastrow = Range("b" & ActiveSheet.Rows.Count).End(xlUp).Row
    Rows(lastrow + 1).Delete
    Rows(1).Insert shift:=xlDown
    Cells(1, 1) = "Begin"
    
    Rows(1).Insert shift:=xlDown
    Cells(1, 1).Value = "INSTRUCTIONS"
    Cells(lastrow + 3, 1).Value = "END"
    
    'Save output to TXT
    Dim record As String
    Open NewName For Output As #1
    newlastrow = Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row
    
    For i = 1 To newlastrow
    If i = 1 Or i = 2 Or i = newlastrow Then
        record = Cells(i, 1).Value
    Else
        record = Cells(i, 1).Value & Cells(i, 2).Value & Cells(i, 3).Value & Cells(i, 4).Value & Cells(i, 5).Value & Cells(i, 6).Value & " - " & Cells(i, 7).Value
    End If
    Print #1, record
    Next i
    Close #1
    

ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

all i need to add now is to MODIFY the "Record" to say the right things

and also if column C = "beeps" then I need it to reference H and within H i need it to make a new entry.

say H = J1-1, J2-2, J3-3, J3-4

then I need it to say:
451 Ground - A should connect to J1-1
451 Ground - A should connect to J2-2

it needs to make it a new line

it's like comma delemited text .

lastly if the background color of EFG does not equal white then the label needs to have the column header added to the Label.

1. modify record
2. "beeps" broken down
3. EFG Headers

I can do the #1 but I am confused on the #2 and #3. Any ideas?

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

PHV · Mar 6, 2008

If Left(Range("c" & i), 2) = "J1" And _
Left(Range("c" & i), 2) = "J2" And _
Left(Range("c" & i), 2) = "J3" And _
Left(Range("c" & i), 2) = "PI" And _
Left(Range("c" & i), 4) = "HFV6" And _
Left(Range("c" & i), 3) = "FPR" And _
Left(Range("c" & i), 6) = "SIDI E" And _
Left(Range("c" & i), 6) = "SIDI O" And _
Left(Range("c" & i), 3) = "TCM" And _
Left(Range("c" & i), 3) = "APP" And _
Left(Range("c" & i), 3) = "API" Then
Cells(i, 2).Value = "$" & Cells(i, 2).Value
End If
I'd replace all the And with Or ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

mattloflin · Mar 6, 2008

thanks, I had just added those!

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

SkipVought · Mar 6, 2008

IF left(A32,3) = left(A33,3) then?????

If the total length of the cell - 1 (take off last letter) = total length of the next cell then

These do not say the same thing.

then add it to a <string>

if 340b (340) = 340c (340) add it to list

These do not say the same thing.

Matt, what do you REALLY mean?

I'm guessing that you mean

Code:

IF left(A32,len(A32)-1) = left(A33,len(a33)-1) then
   'append to some list, but what list?
   'as I studied your first download example, I at first
   'thought that it was the list in column P 
   '"somewhat" adjacent to the data in column F
   'but then I observed the data in rows 48-59
   'and that theory got shot down.

   'so now I'm looking at your last post and it seems
   'like a TOTALLY different issue.
 
   'I am TOTALLY comfused!
end if

Skip,
_{I'll be dressed to the nines this week, as I go to have my prostatectomy...

Because, if I'm gonna BE impotent, I want to LOOK impotent!}

SkipVought · Mar 6, 2008

Just a thought. I joined your two lists...

Code:

SELECT Label, Terminal, `Cable Seal`, `Pin#`, AWG, Circuit, Color, LEN
FROM `C:\Documents and Settings\ii36250\My Documents\vba\Splice Sheet`.`'Build Sheet$'` 
where Label is not null

UNION

SELECT Label1, Terminal1, `Cable Seal1`, `Pin#1`, AWG1, Circuit1, Color1, LEN1
FROM `C:\Documents and Settings\ii36250\My Documents\vba\Splice Sheet`.`'Build Sheet$'` 
where Label1 is not null

Frome here, or something like it, it should be much easier to build what you want.

Skip,
_{I'll be dressed to the nines this week, as I go to have my prostatectomy...

Because, if I'm gonna BE impotent, I want to LOOK impotent!}

mattloflin · Mar 6, 2008

good ol skip!

Well actually the first post changed as we went throuhg. I found a document that they make (they the people in the back) and i can use it and it's a lot easier.

here is the code that i'm working on:

Code:

    Dim ocell As Range
    newlastrow = Range("c" & ActiveSheet.Rows.Count).End(xlUp).Row
    
    For i = newlastrow To 2 Step -1
        If Left(Range("c" & i), 2) <> "J1" And _
           Left(Range("c" & i), 2) <> "J2" And _
           Left(Range("c" & i), 2) <> "J3" And _
           Left(Range("c" & i), 2) <> "PI" And _
           Left(Range("c" & i), 4) <> "HFV6" And _
           Left(Range("c" & i), 3) <> "FPR" And _
           Left(Range("c" & i), 6) <> "SIDI E" And _
           Left(Range("c" & i), 6) <> "SIDI O" And _
           Left(Range("c" & i), 3) <> "TCM" And _
           Left(Range("c" & i), 3) <> "APP" And _
           Range("c" & i) Like "*" & "beep" & "*" And _
           Left(Range("c" & i), 3) <> "API" Then
               Range(i & ":" & i).EntireRow.Delete
        End If
        If Left(Range("c" & i), 2) = "J1" Or _
           Left(Range("c" & i), 2) = "J2" Or _
           Left(Range("c" & i), 2) = "J3" Or _
           Left(Range("c" & i), 2) = "PI" Or _
           Left(Range("c" & i), 4) = "HFV6" Or _
           Left(Range("c" & i), 3) = "FPR" Or _
           Left(Range("c" & i), 6) = "SIDI E" Or _
           Left(Range("c" & i), 6) = "SIDI O" Or _
           Left(Range("c" & i), 3) = "TCM" Or _
           Left(Range("c" & i), 3) = "APP" Or _
           Range("c" & i) Like "beep" Or _
           Left(Range("c" & i), 3) = "API" Then
                Cells(i, 3).Value = "$" & Cells(i, 3).Value
        End If

        If Range("e" & i).Interior.ColorIndex <> -4142 Then
            Cells(i, 9) = Cells(i, 9) & " " & Left(Cells(1, 5), 2)
        End If
        If Range("f" & i).Interior.ColorIndex <> -4142 Then
            Cells(i, 9) = Cells(i, 9) & " " & Left(Cells(1, 6), 1)
        End If
        If Range("g" & i).Interior.ColorIndex <> -4142 Then
            Cells(i, 9) = Cells(i, 9) & " " & Left(Cells(1, 7), 1)
        End If
    Next i
    
    Rows(1).Delete
    lastrow = Range("b" & ActiveSheet.Rows.Count).End(xlUp).Row
    Rows(lastrow + 1).Delete
    Rows(1).Insert shift:=xlDown
    Cells(1, 1) = "Begin"
    
    Rows(1).Insert shift:=xlDown
    Cells(1, 1).Value = "INSTRUCTIONS"
    Cells(lastrow + 3, 1).Value = "END"
    
    'Save output to TXT
    Dim record As String
    Open NewName For Output As #1
    newlastrow = Range("a" & ActiveSheet.Rows.Count).End(xlUp).Row
    
    For i = 1 To newlastrow
    If i = 1 Or i = 2 Or i = newlastrow Then
        record = Cells(i, 1).Value
    ElseIf Cells(i, 9).Value = "" Then
        record = "PROBETESTPOINT," & Cells(i, 3).Value & ",COLOR," & Cells(i, 4).Value & ",LABEL," & Cells(i, 1).Value & " - " & Cells(i, 2).Value
    Else
        record = "PROBETESTPOINT," & Cells(i, 3).Value & ",COLOR," & Cells(i, 4).Value & ",LABEL," & Cells(i, 1).Value & " - " & Cells(i, 2).Value & " - " & Cells(i, 9).Value
    End If
    Print #1, record
    Next i
    Close #1

I can't get the:

Code:

        If Left(Range("c" & i), 2) <> "J1" And _
           Left(Range("c" & i), 2) <> "J2" And _
           Left(Range("c" & i), 2) <> "J3" And _
           Left(Range("c" & i), 2) <> "PI" And _
           Left(Range("c" & i), 4) <> "HFV6" And _
           Left(Range("c" & i), 3) <> "FPR" And _
           Left(Range("c" & i), 6) <> "SIDI E" And _
           Left(Range("c" & i), 6) <> "SIDI O" And _
           Left(Range("c" & i), 3) <> "TCM" And _
           Left(Range("c" & i), 3) <> "APP" And _
           Range("c" & i) Like "*" & "beep" & "*" And _
           Left(Range("c" & i), 3) <> "API" Then
               Range(i & ":" & i).EntireRow.Delete
        End If

this doesn't work the Range("c"&i) like "beep"

I need it to search the cell if the cell has "beep" in it's contents then to skip it. With the code i currently have it doesn't delete the empty row because of the Beep line i have.

second - if it does equal beeps then i need it to grab the number (ex. 3 beeps, it should grab the 3) and insert three rows the three rows should then brake apart the H column (ex. H = J1-3, J2-3, J3-73) and insert the first J1-3 into the first row, second into second and third into third.
I can do the rest after that.

once those two things are done i'm about 80% complete.

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

mattloflin · Mar 6, 2008

here is the new list that we are working from SKIP

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

SkipVought · Mar 6, 2008

Code:

If Cells(i, "C").value like "*beep*" then

Skip,
_{I'll be dressed to the nines this week, as I go to have my prostatectomy...

Because, if I'm gonna BE impotent, I want to LOOK impotent!}

mattloflin · Mar 6, 2008

i now have this:

Code:

        If Left(Range("c" & i), 2) <> "J1" And _
           Left(Range("c" & i), 2) <> "J2" And _
           Left(Range("c" & i), 2) <> "J3" And _
           Left(Range("c" & i), 2) <> "PI" And _
           Left(Range("c" & i), 4) <> "HFV6" And _
           Left(Range("c" & i), 3) <> "FPR" And _
           Left(Range("c" & i), 6) <> "SIDI E" And _
           Left(Range("c" & i), 6) <> "SIDI O" And _
           Left(Range("c" & i), 3) <> "TCM" And _
           Left(Range("c" & i), 3) <> "APP" And _
           Cells(i, "C").Value Like "*beep*" And _
           Left(Range("c" & i), 3) <> "API" Then
               Range(i & ":" & i).EntireRow.Delete
        End If

and it doesn't delete any rows at all. Ideas?

- Matt

"Never Give a Sword to a Man Who Can't Dance"

Win 98/2000/2003 Server/XP/Vista, Active Directory
- VB6, Access, Excel, HTML
- Dreamweaver, Fireworks
OS X 10.4/10.5
Linux Red Hat, Xubuntu, Ubuntu

SkipVought · Mar 6, 2008

Where is "beep" in ANY column c value in the newly referenced workbook?

Skip,
_{I'll be dressed to the nines this week, as I go to have my prostatectomy...

Because, if I'm gonna BE impotent, I want to LOOK impotent!}

SkipVought · Mar 6, 2008

sorry I found it.

Skip,
_{I'll be dressed to the nines this week, as I go to have my prostatectomy...

Because, if I'm gonna BE impotent, I want to LOOK impotent!}

SkipVought · Mar 6, 2008

How about...

Code:

           UCase(Range("c" & i)) Like "*BEEP*" And _

Skip,
_{I'll be dressed to the nines this week, as I go to have my prostatectomy...

Because, if I'm gonna BE impotent, I want to LOOK impotent!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

IF left(A32,3) = left(A33,3) then????? 2

MIS

MIS

Technical User

MIS

MIS

MIS

Technical User

MIS

MIS

MIS

MIS

Programmer

Programmer

MIS

MIS

Programmer

MIS

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor