Find multiple / replace adjacent?

kod2th3e · Oct 6, 2011

[FONT=&quot]Do you know of a formula or VB code that I can tell it to find all instances of an assembly number ex. “10651457-011” and replace it’s build qty in the cell next to it? So far, I can use an input box to gather what assembly to look for and another input box to gather what I want the build qty to be. The problem I have is finding all instances of the assembly number and replacing the cell adjacent to it with a new number. Thoughts? Thanks for looking.

[/FONT] <table class="MsoNormalTable" style="width:266.0pt;margin-left:-.65pt;border-collapse:collapse;border:none; mso-border-alt:solid windowtext .5pt;mso-yfti-tbllook:1184;mso-padding-alt: 0in 5.4pt 0in 5.4pt;mso-border-insideh:.5pt solid windowtext;mso-border-insidev: .5pt solid windowtext" border="1" cellpadding="0" cellspacing="0" width="355"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> Bin Number
</td> <td style="width:78.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> Part Number
</td> <td style="width:78.25pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> Assembly Number
</td> <td style="width:48.0pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64"> Build Qty
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry128
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 128-128-128
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-021
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry123
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 123-123-123
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-021
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry124
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 124-124-124
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-021
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry125
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 125-125-125
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-021
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:5;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry126
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 126-126-126
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-011
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:6;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry127
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 127-127-127
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-021
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:7;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry129
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 129-129-129
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-021
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> <tr style="mso-yfti-irow:8;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:61.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="81"> stry130
</td> <td style="width:78.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="105"> 130-130-130
</td> <td style="width:78.25pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="104"> 10651457-011
</td> <td style="width:48.0pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="64">

10

</td> </tr> </tbody></table>

venkat1926 · Oct 7, 2011

It is not clear why you want a second inputbox for "build qty" because it is already there in column D

your data is like this in sheet1 (copy data in sheet 1 to sheet 2 also)

Excel Workbook

A

B

C

D

1

Bin Number

Part Number

Assembly Number

Build Qty

2

stry128

128-128-128

10651457-021

10

3

stry123

123-123-123

10651457-021

10

4

stry124

124-124-124

10651457-021

10

5

stry125

125-125-125

10651457-021

10

6

stry126

126-126-126

10651457-011

10

7

stry127

127-127-127

10651457-021

10

8

stry129

129-129-129

10651457-021

10

9

stry130

130-130-130

10651457-011

10

Sheet1

now try this macro

Code:

Sub test()
Dim cfind As Range, add As String, x
Worksheets("sheet1").Activate
x = InputBox("type the nummber you want e.g.10651457-011")
Set cfind = Columns("C:C").Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
cfind = cfind.Offset(0, 1)
End If
Do
Set cfind = Columns("C:C").FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfind = cfind.Offset(0, 1)
Loop
End Sub

Code:

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
End Sub

be careful when you are inputting in the input box. preferably copy it in the clipboard and use it for pasting in the input box.
<table border="0" cellpadding="0" cellspacing="0" width="133"><tr height="17"><td style="height:12.75pt;width:100pt" height="17" width="133">
</td> </tr></table>

kod2th3e · Oct 7, 2011

What I would like to do is type/scan an assembly number into an input box, find all instances of it, and replace it's build quantity with another input from an input box. So for instance if the 10651457-011 assembly (there are going to be multiple assemblies in this list) is usually a build quantity of 10 and now is 5, I'd like to be able to change that all at once instead of manually searching for all occurrences of it and manually changing it's build quantity.

venkat1926 · Oct 7, 2011

did you test my macro.
test it and then comment

my macro does find all occurrences of .......-11.

now I though build quantity is is column d need not necesarily be same for each of the input value. so it will take the data from the next column for each find of this number.

if you have some more explanations post back.

kod2th3e · Oct 7, 2011

When I ran this it replaced the all of the assembly number with the build quantity next to it. I just want it to replace the build quantity with a new build quantity that is provided by the user.

kod2th3e · Oct 7, 2011

Here is what I ended up doing:

Code:

Sub test()
Dim cfind As Range, add As String, x
[I][COLOR=Red]Dim strnewqty As String[/COLOR][/I]

Worksheets("trial").Activate
x = InputBox("Please input the assembly number you want to update the build quantity for.", "Update quantity")
[I][COLOR=Red]strnewqty = InputBox("What is the new build quantity?", "New quantity?")[/COLOR][/I]

Set cfind = Columns("C:C").Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
[I][COLOR=Red]cfind.Offset(0, 1).Value = strnewqty[/COLOR][/I]
End If
Do
Set cfind = Columns("C:C").FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
[I][COLOR=Red]cfind.Offset(0, 1).Value = strnewqty[/COLOR][/I]
Loop
End Sub

I added a new variable (strnewqty) and made it an user input through the use of an inputbox. Then, I replaced the cfind=cfind.offset(0,1) with cfind.offset(0,1).value = strnewqty. I couldn't have gotten this far without your help, I appreciate your time.

Thanks again,
-Cody

Find multiple / replace adjacent?

kod2th3e

Board Regular

Excel Facts

venkat1926

Well-known Member

kod2th3e

Board Regular

venkat1926

Well-known Member

kod2th3e

Board Regular

kod2th3e

Board Regular

Similar threads

Forum statistics

Share this page

Find multiple / replace adjacent?

kod2th3e

Board Regular

Excel Facts

venkat1926

Well-known Member

kod2th3e

Board Regular

venkat1926

Well-known Member

kod2th3e

Board Regular

kod2th3e

Board Regular

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