* Help Fixing VBA * (Code within to include Excel examples)

gcbutter · Mar 7, 2015

I need your help guys. I know I'm not a vba expert, however, I know I hate trying to fix another persons code. I will paste example copies of the .xl pages with the vba coding next followed by the desired output last. Synoposis of product: Tab A (labeled "A Build") is the product line info. Tab B (Labeled "NB") is the product NB Name. I need to find a way to adjust the below vba so I can get the following result: if the NB name (Tab B "NB") is listed on Tab A ("A Build") then the vba will take the NB ID (Column I) and copy it over to Column M (same row adjacent).

Tab A ("A Build")

A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>
Item ID</SPAN>	NB #</SPAN>	NB #</SPAN>	Item Name</SPAN>	Item Loc</SPAN>	Item Dest</SPAN>	Item/Rec Time</SPAN>	Air Cargo</SPAN>	NB ID</SPAN>
P4532</SPAN>	C211</SPAN>	C211</SPAN>	Spider</SPAN>	OERK</SPAN>	OFGH</SPAN>			4352</SPAN>
A2158</SPAN>	C211</SPAN>	C211</SPAN>	David</SPAN>	OERK</SPAN>	OFGH</SPAN>			4353</SPAN>
PZ4895</SPAN>	C211</SPAN>	C211</SPAN>	Purse</SPAN>	OERK</SPAN>	OFGH</SPAN>			4354</SPAN>
A4258</SPAN>	C451</SPAN>	C451</SPAN>	Bear</SPAN>	OERK</SPAN>	OFGH</SPAN>			4355</SPAN>
FE4525</SPAN>	C321</SPAN>	C321</SPAN>	Tear</SPAN>	OERK</SPAN>	OFGH</SPAN>			4356</SPAN>
P4875</SPAN>	C487</SPAN>	C487</SPAN>	Taco</SPAN>	OERK</SPAN>	OFGH</SPAN>			3530</SPAN>
P6985</SPAN>	C585</SPAN>	C585</SPAN>	Zipper</SPAN>	OERK</SPAN>	OFGH</SPAN>			3531</SPAN>
PO4789</SPAN>	C459</SPAN>	C459</SPAN>	Punch</SPAN>	OERK</SPAN>	OFGH</SPAN>			3532</SPAN>
AZ4823</SPAN>	C596</SPAN>	C596</SPAN>	Apexi</SPAN>	OERK</SPAN>	OFGH</SPAN>			3533</SPAN>
P7524</SPAN>	HELV</SPAN>	C417</SPAN>	Drac</SPAN>	OERK</SPAN>	OFGH</SPAN>			3534</SPAN>

Tab B ("NB")

A</SPAN></SPAN>	B</SPAN></SPAN>	C</SPAN></SPAN>	D</SPAN></SPAN>	E</SPAN></SPAN>
AG NB (Category Line)</SPAN>	FV NB</SPAN>	SA NB</SPAN>	SO NB</SPAN>	TF NB</SPAN>
Spider</SPAN>	Caso</SPAN>	Carne</SPAN>	Heat</SPAN>	Opps</SPAN>
David</SPAN>	Turkey</SPAN>	Lego</SPAN>		Unit</SPAN>
Purse</SPAN>	Drac</SPAN>	Almond</SPAN>		Fargo</SPAN>
Macon</SPAN>	Bell</SPAN>	Punch</SPAN>		Have</SPAN>
Twilight</SPAN>		Pow</SPAN>		Voice</SPAN>
Express</SPAN>		Tweet</SPAN>		Mail</SPAN>
		Grand</SPAN>		Pin</SPAN>
		Hover</SPAN>		Bear</SPAN>
				Tear</SPAN>

VBA

Code:

'Format N3's / Build NB
    Application.StatusBar = "FORMATTING N3"
    For x = 2 To ActiveSheet.UsedRange.Rows.Count + 1
    chkrowA = Rows.Item(x).Cells(1, 1) 'Item ID
    chkrowB = Rows.Item(x).Cells(1, 2) 'NB #
    chkrowl = Rows.Item(x).Cells(1, 9) 'NB ID
    
    'Format N3s
    If chkrowl <> "" Then
       chkrowll = Right(Rows.Item(x).Cells(1, 9), 4)
       Rows.Item(x).Cells(1, 9) = chkrowl
    End If
    
    'Select N3s for NB
       Application.StatusBar = "Selecting M2s FOR NB"
     If((Left(chkrowA,1)="P" or Left(chkrowA,1)="A")And chkrowB="NBL")_
       Or Left(chkrowA,2)="AX" Or Left(chkrowA,2)="PX" Or Left(chkrowA,2)="TX"
       Or ((Left(chkrowA,2)="AF" Or Left(chkrowA,2)="AM" Or Left(chkrowA,2)="AZ") And chkrowB
    <>"HELV")Then
    
       Rows.Item(x).Cells(1, 9).Select
       Selection.Copy
       Rows.Item(x).Cells(1, 13).Select
       ActiveSheet.Paste
    End If
    
    Next

Desired Output

A

B

C

D

E

F

G

H

I

J

K

L

M

Item ID

NB #

Item Name

Item Loc

Item Dest

Item/R Time

Air Cargo

NB ID

P4532

C211

Spider

OERK

OFGH

4352

A2158

C211

David

OERK

OFGH

4353

PZ4895

C211

Purse

OERK

OFGH

4354

A4258

C451

Bear

OERK

OFGH

4355

FE4525

C321

Tear

OERK

OFGH

4356

P4875

C487

Taco

OERK

OFGH

3530

P6985

C585

Zipper

OERK

OFGH

3531

PO4789

C459

Punch

OERK

OFGH

3532

AZ4823

C596

Apexi

OERK

OFGH

3533

P7524

HELV

C417

Drac

OERK

OFGH

3534

Thank you for all your help in this...

gcbutter · Mar 7, 2015

I'm using Office 2010

gcbutter · Mar 8, 2015

I know this is an easy kill for some of you guys. Please help... The company is changing direction on data processing and without this code, my job will be alot more difficult.

NoSparks · Mar 8, 2015

Maybe something like this ?

Code:

For Each cel In Sheets("A Build").Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row)
    With Sheets("NB").Cells
        Set FoundIt = .Find(What:=cel.Value, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
        If Not FoundIt Is Nothing Then  '~~> FOUND IT
            With Sheets("A Build")
                .Cells(cel.Row, 13).Value = .Cells(cel.Row, 9).Value
            End With
        End If
    End With
Next

gcbutter · Mar 8, 2015

First, thank you for helping me. Unfortunutly, the code provided does not give me the associated NB ID ("A Build") from those products listed in Sheet ("NB"). Instead the code below listed every products NB ID instead of only those on the "NB" sheet. Thoughts???

NoSparks said:

Maybe something like this ?

Code:

For Each cel In Sheets("A Build").Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row)
    With Sheets("NB").Cells
        Set FoundIt = .Find(What:=cel.Value, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
        If Not FoundIt Is Nothing Then  '~~> FOUND IT
            With Sheets("A Build")
                .Cells(cel.Row, 13).Value = .Cells(cel.Row, 9).Value
            End With
        End If
    End With
Next

NoSparks · Mar 9, 2015

can you elaborate and/or explain this ?

I need to find a way to adjust the below vba so I can get the following result: if the NB name (Tab B "NB") is listed on Tab A ("A Build") then the vba will take the NB ID (Column I) and copy it over to Column M (same row adjacent).

The example you have for "A Build" contains only 1 row that satisfies the IF of the code you posted and the item name from that row is not on "NB" so if that IF statement is to be considered I would think the required results would not have any rows with anything copied to column M.

If the IF statement is not to be considered and the above quote interpreted as it reads, which items should not have anything copied into column M ?

gcbutter · Mar 9, 2015

We changed or methods on identifing product ID. Everything used to be on one sheet and now we have two sheets. The old code's IF statement took indicators per row to ID the product. Simply, I just want to replace the below piece of code so as to take the product names on Sheet ("NB") and list the corresponding ID number (found on Column D) on sheet ("AT Build") within Column M on the same sheet. The products names will vary per day on the NB sheet... With that, I'm hoping the rest of the code should continue as it normally did.

Code:

       Application.StatusBar = "Selecting M2s FOR NB"
     If((Left(chkrowA,1)="P" or Left(chkrowA,1)="A")And chkrowB="NBL")_
       Or Left(chkrowA,2)="AX" Or Left(chkrowA,2)="PX" Or Left(chkrowA,2)="TX"
       Or ((Left(chkrowA,2)="AF" Or Left(chkrowA,2)="AM" Or Left(chkrowA,2)="AZ") And chkrowB
    <>"HELV")Then

       Rows.Item(x).Cells(1, 9).Select
       Selection.Copy
       Rows.Item(x).Cells(1, 13).Select
       ActiveSheet.Paste

Thank you and I look foward to your reply.

NoSparks said:
can you elaborate and/or explain this ?

The example you have for "A Build" contains only 1 row that satisfies the IF of the code you posted and the item name from that row is not on "NB" so if that IF statement is to be considered I would think the required results would not have any rows with anything copied to column M.

If the IF statement is not to be considered and the above quote interpreted as it reads, which items should not have anything copied into column M ?

NoSparks · Mar 9, 2015

and what is the answer to the last line of my previous post ?

If it really is what you originally posted as the required result, why does the item named "Tear" not qualify ?

gcbutter · Mar 9, 2015

I'm sorry, "Tear" does qualify... I must have missed it.

QUOTE=NoSparks;4096918]and what is the answer to the last line of my previous post ?

If it really is what you originally posted as the required result, why does the item named "Tear" not qualify ?[/QUOTE]

gcbutter · Mar 9, 2015

The answer to the last post is: If the name does not appear on the "NB" sheet, then the product ID will not be listed in Column M on the "AT Build" sheet.

NoSparks said:
and what is the answer to the last line of my previous post ?

If it really is what you originally posted as the required result, why does the item named "Tear" not qualify ?

* Help Fixing VBA * (Code within to include Excel examples)

gcbutter

New Member

Excel Facts

gcbutter

New Member

gcbutter

New Member

NoSparks

Well-known Member

gcbutter

New Member

NoSparks

Well-known Member

gcbutter

New Member

NoSparks

Well-known Member

gcbutter

New Member

gcbutter

New Member

Similar threads

Forum statistics

Share this page

*** Help Fixing VBA *** (Code within to include Excel examples)

New Member

Excel Facts

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

* Help Fixing VBA * (Code within to include Excel examples)