* 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
[TABLE="width: 649"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]Item ID
[/TD]
[TD]NB #
[/TD]
[TD]NB #
[/TD]
[TD]Item Name
[/TD]
[TD]Item Loc
[/TD]
[TD]Item Dest
[/TD]
[TD]Item/R Time
[/TD]
[TD]Air Cargo
[/TD]
[TD]NB ID
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4532
[/TD]
[TD]C211
[/TD]
[TD]C211
[/TD]
[TD]Spider
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4352
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4352
[/TD]
[/TR]
[TR]
[TD]A2158
[/TD]
[TD]C211
[/TD]
[TD]C211
[/TD]
[TD]David
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4353
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4353
[/TD]
[/TR]
[TR]
[TD]PZ4895
[/TD]
[TD]C211
[/TD]
[TD]C211
[/TD]
[TD]Purse
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4354
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4354
[/TD]
[/TR]
[TR]
[TD]A4258
[/TD]
[TD]C451
[/TD]
[TD]C451
[/TD]
[TD]Bear
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4355
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4355
[/TD]
[/TR]
[TR]
[TD]FE4525
[/TD]
[TD]C321
[/TD]
[TD]C321
[/TD]
[TD]Tear
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4356
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4875
[/TD]
[TD]C487
[/TD]
[TD]C487
[/TD]
[TD]Taco
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3530
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P6985
[/TD]
[TD]C585
[/TD]
[TD]C585
[/TD]
[TD]Zipper
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3531
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO4789
[/TD]
[TD]C459
[/TD]
[TD]C459
[/TD]
[TD]Punch
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3532
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3532
[/TD]
[/TR]
[TR]
[TD]AZ4823
[/TD]
[TD]C596
[/TD]
[TD]C596
[/TD]
[TD]Apexi
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3533
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P7524
[/TD]
[TD]HELV
[/TD]
[TD]C417
[/TD]
[TD]Drac
[/TD]
[TD]OERK
[/TD]
[TD]OFGH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3534
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3534
[/TD]
[/TR]
</TBODY>[/TABLE]

Thank you for all your help in this...

gcbutter · Mar 11, 2015

Anyone? I have been racking my brain on this with little luck. I know there are some people on this Forum with true skill at vba... Please assist.

NoSparks · Mar 11, 2015

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.

try replacing it with this

Code:

    With Sheets("NB").Cells
        Set FoundIt = .Find(What:=Rows.Item(x).Cells(1, 4).Value, LookIn:=xlValues, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
        If Not FoundIt Is Nothing Then 
            ActiveSheet.Cells(x, 13).Value = ActiveSheet.Cells(x, 9).Value
        End If
    End With

If that doesn't work you'll need to make a workbook available.

gcbutter · Mar 12, 2015

NoSparks, Thank you! I must have been overthinking it and your simple approach has solved my problem. Thank you again, the code works great!

Side note: Where did you receive your vba training from?

NoSparks said:

try replacing it with this

Code:

    With Sheets("NB").Cells
        Set FoundIt = .Find(What:=Rows.Item(x).Cells(1, 4).Value, LookIn:=xlValues, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
        If Not FoundIt Is Nothing Then 
            ActiveSheet.Cells(x, 13).Value = ActiveSheet.Cells(x, 9).Value
        End If
    End With

If that doesn't work you'll need to make a workbook available.

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

gcbutter

New Member

gcbutter

New Member

NoSparks

Well-known Member

gcbutter

New Member

Similar threads

Forum statistics

Share this page

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

gcbutter

New Member

gcbutter

New Member

NoSparks

Well-known Member

gcbutter

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)