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

G

gcbutter

New Member
Joined
Mar 4, 2015
Messages
16
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>

<TBODY>
</TBODY>

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>

<TBODY>
</TBODY>

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</SPAN>
B</SPAN>
C</SPAN>
D</SPAN>
E</SPAN>
F</SPAN>
G</SPAN>
H</SPAN>
I</SPAN>
J</SPAN>
K</SPAN>
L</SPAN>
M</SPAN>
Item ID</SPAN>
NB #</SPAN>
NB #</SPAN>
Item Name</SPAN>
Item Loc</SPAN>
Item Dest</SPAN>
Item/R Time</SPAN>
Air Cargo</SPAN>
NB ID</SPAN>
P4532</SPAN>
C211</SPAN>
C211</SPAN>
Spider</SPAN>
OERK</SPAN>
OFGH</SPAN>
4352</SPAN>
4352</SPAN>
A2158</SPAN>
C211</SPAN>
C211</SPAN>
David</SPAN>
OERK</SPAN>
OFGH</SPAN>
4353</SPAN>
4353</SPAN>
PZ4895</SPAN>
C211</SPAN>
C211</SPAN>
Purse</SPAN>
OERK</SPAN>
OFGH</SPAN>
4354</SPAN>
4354</SPAN>
A4258</SPAN>
C451</SPAN>
C451</SPAN>
Bear</SPAN>
OERK</SPAN>
OFGH</SPAN>
4355</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>
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>
3534</SPAN>

<TBODY>
</TBODY>


Thank you for all your help in this...
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
Click to expand...
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.
 
Upvote 0
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? </SPAN>


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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
*** Copy and Paste Help *** Excel Example Within
2
Replies
12
Views
887
MARK858
MARK858

Forum statistics

Threads
1,215,945
Messages
6,127,844
Members
449,411
Latest member
adunn_23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top