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

it still doesn't delete the rows. Basically if it has the word "beep" in it then it needs to skip over it.

I have blank rows that are being skipped.

- 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

Like this ?
LCase(Range("c" & i)) Not Like "*beep*" And _

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

mattloflin · Mar 6, 2008

i talked to our Quality Control Manager (finally) and he says that it will always say "# Beeps" the # being a number

so i just used

Code:

Right(Range("c" & i), 5) <> "BEEPS" And _

now if it equals "Beeps" i need it to read Column H

column H looks like this - J1-4, J2-3, J3-23

and column h can vary and have more J1 or J2 or J3 etc.

Basically I need excel to insert a new row for the number of connectors defined in range("c",i) so say its 5 i need it to insert 5 rows and copy J1-4 to the first into column C and copy J2-3 into column C but the second row. after the five have been pasted i need it to delete the original one that says Beeps.

- 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

i can take care of the deleting of the original i can't figure out the reading of the j1-4 etc and pasting those into the appropriate cells

- 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

You DO want beep rows deleted???

Code:

(UCase(Range("c" & i)) Like "*BEEP*" Or Cells(i, "C") = "") 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!}

mattloflin · Mar 6, 2008

I want Beep rows deleted AFTER i use the information in Column H.

This is what i have so far

Code:

        If Right(Range("c" & i), 5) = "BEEPS" Then
            NConnect = Left(Range("c" & i), 2)
            For n = 0 To NConnect
                Rows(i).Insert shift:=xlDown
            Next n
            Connect = Cells(i, "h").Value
        End If

n is declared as an integer
NConnect holds the number of rows to insert
Connect holds the content of H

Now I need code to break down H and insert it into the cells that are now above the "Beeps" row.

follow me?

Don't worry about the Deleting of Beeps I can take care of it I just need an idea about the breaking down of column H

- 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

geez, the target keeps moving!!!!

How about we start from square one.

Describe the problem.

Post the sample data.

Show the desired result.

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

Sorry so many variables in this one.

TARGET:

Code:

        If Right(Range("c" & i), 5) = "BEEPS" Then
            NConnect = Left(Range("c" & i), 2)
            For n = 1 To NConnect
                Rows(i).Select
                Rows(i).Copy
                Rows(i).Insert shift:=xlDown
            Next n
            Connect = Cells(i, "h").Value
        End If

This inserts the right number of rows above the original "Beeps"

then "Connect" is the value in column H

I need it to brake that down into however many sections as needed

ex. J1-3, J2-45, J3-66
Thats 3 connectors (it'll sometimes be 4 7, 13 etc)

I need it to paste J1-3 into Column C of on of the inserted rows
J2-45 into another and J3-66 into another.

(attached) - is the before and after

- 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

Did you take a look at the UNION Sql I posted. For instance, here's the result for 139...
[tt]
Label Terminal Cable Seal Pin# AWG Circuit Color LEN
Power In 15304720 12048086 B 12 139A PK 35.3
Fuel Injector # 1 12191819 15366021 A 18 139B PK 50
Fuel Injector # 2 12191819 15366021 A 18 139C PK 50
Fuel Injector # 3 12191819 15366021 A 18 139D PK 50
Fuel Injector # 4 12191819 15366021 A 18 139E PK 50
Fuel Injector # 5 12191819 15366021 A 18 139F PK 50
Fuel Injector # 6 12191819 15366021 A 18 139G PK 50
Fuel Injector # 7 12191819 15366021 A 18 139H PK 50
Fuel Injector # 8 12191819 15366021 A 18 139J PK 50
[/tt]
Where would you go with this?

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 noticed that it uses two sheets that we are not using for this project anymore.

The sheet i'm working off of now is attached.

Instead of working off two sheets its working off one.

I have attached a zip file with the macro and original sheet attached.

Run them and tell me what you think.

GTS.bas - Macro
Test Sheet.xls - Run the macro on this document
B4nAfter.xls - what I need to do to the BEEP row
Test Sheet GTS.xls - the output to a TXT File

- 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

What should I be looking for? What is not working?

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

this code displays "test" in the two inserted rows

Code:

        If Right(Range("c" & i), 5) = "BEEPS" Then
            nconnect = Left(Range("c" & i), 2)
            For n = 1 To nconnect
                Rows(i).Select
                Rows(i).Copy
                Rows(i).Insert shift:=xlDown
            Next n
            Connect = Cells(i, "h").Value
            For m = 1 To nconnect
                p = m + i
                Cells(p, 3).Value = "test"
            Next m
        End If

so it's inserting the rows based on the number in the "beeps" cell

so really all i need now is it to insert the info in column H instead of "test"

- 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

the string "Connect" is storing the information in column H

I need it to be broken down into different pieces and placed into the inserted rows into column C

so say H3 = "j1-3, j3-7, j2-6"

then inserted row cell c is j1-3
inserted row #2 cell c is j3-7
inserted row #3 cell c is j2-6

- 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

all the connectors are seperated by a comma. so if we could store them as string based off the commas so my previous example would have 3 strings and it'd paste 1 into cell 1 2 into cell 2 etc.

how could i store a variable based off a comma? some of the connectors are 5 digits long some are 4. So they are either 4 or 5 digits long.

- 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

my bad... they are seperated by a comma space

ex. j1-13, j2-3, j3-73

- 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 Right(Range("c" & i), 5) = "BEEPS" Then
            NConnect = Left(Range("c" & i), 2)
            For n = 1 To NConnect - 1
                Rows(i).Copy
                Rows(i).Insert shift:=xlDown
            Next n
            Connect = Split(Cells(i, "h").Value, ",")
            For m = 0 To UBound(Connect)
                Cells(i + m, 3).Value = Trim(Connect(m))
            Next m
        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!}

mattloflin · Mar 6, 2008

it says Expected Array and highlights for m-0 to UBound(Connect)

- 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

i had connect dimmed as a string... my bad!

- 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

Sorry, I sould have included

Code:

  Dim Connect

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

MIS

MIS

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

MIS

MIS

MIS

Programmer

MIS

MIS

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor