Need a hand with matching cells

Sort by date Sort by votes
austin397

Your reply #10 does not show what your results (manually formatted by you) should look like?
 
Upvote 0
I'm not sure what you mean, I manually entered the information that I am wanting to be automatically added. The information in the blue cells are what I would like to have auto generate based on what is in the white cells. Cells B2:F2 should generate this, when cells A6:A19 contain that.

austin397 said:
Sorry, guess I should have know to do that as well.

Excel 2007
ABCDEF

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Fab 1:[/TD]
[TD="align: center"]Fab 2:[/TD]
[TD="align: center"]Fab 3:[/TD]
[TD="align: center"]Fab 4:[/TD]
[TD="align: center"]Fab 5:[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #8DB4E3, align: center"]Tee[/TD]
[TD="bgcolor: #8DB4E3, align: center"]90 Degree[/TD]
[TD="bgcolor: #8DB4E3, align: center"]Angle Fab[/TD]
[TD="bgcolor: #8DB4E3, align: center"]4" Side Outlet[/TD]
[TD="bgcolor: #8DB4E3, align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Fabs[/TD]
[TD="bgcolor: #D8D8D8, align: center"]-[/TD]
[TD="bgcolor: #D8D8D8, align: center"]-[/TD]
[TD="bgcolor: #D8D8D8, align: center"]-[/TD]
[TD="bgcolor: #D8D8D8, align: center"]-[/TD]
[TD="bgcolor: #D8D8D8, align: center"]-[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: center"][/TD]
[TD="bgcolor: #D8D8D8, align: center"][/TD]
[TD="bgcolor: #D8D8D8, align: center"][/TD]
[TD="bgcolor: #D8D8D8, align: center"][/TD]
[TD="bgcolor: #D8D8D8, align: center"][/TD]
[TD="bgcolor: #D8D8D8, align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Tee[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Tee[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]90 Degree[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]Angle Fab[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]4" Side Outlet[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

</tbody>
sample​



Thanks!
Click to expand...
 
Last edited:
Upvote 0
I manually entered the information that I am wanting to be automatically added. The information in the blue cells are what I would like to have auto generate based on what is in the white cells. Cells B2:F2 should generate this, when cells A6:A19 contain that.
Click to expand...

austin397,

As you enter text in column A, from A6 down, here is a Worksheet_Change event that will do what you want.

Sample worksheet:


Excel 2007
ABCDEF
1Fab 1:Fab 2:Fab 3:Fab 4:Fab 5:
2
3
4Fabs-----
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
sample


And, as you add the Fab's in column A, one at a time, the final results:


Excel 2007
ABCDEF
1Fab 1:Fab 2:Fab 3:Fab 4:Fab 5:
2Tee90 DegreeAngle Fab4" Side Outlet
3
4Fabs-----
5
6Tee
7Tee
8
9
10
11
1290 Degree
13Angle Fab
14
15
164" Side Outlet
17
18
19
20
sample


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/30/2017, ME981215
Dim f As Range, nc As Long
If Intersect(Target, Range("A6", Range("A" & Rows.Count).End(xlUp))) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
    If Not Target = vbEmpty Then
      Set f = Rows(2).Find(Target.Value, LookAt:=xlWhole)
      If f Is Nothing Then
        nc = Cells(2, .Columns.Count).End(xlToLeft).Column + 1
          Cells(2, nc).Value = Target.Value
          Columns(nc).AutoFit
      End If
    End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then add data in column A, beginning in cell A6, down, one cell at a time.
 
Upvote 0
Thanks hiker95!

That works for the example, now I just need to modify the code a bit to fit with my real sheet which is much more complicated. I just needed a starting point.
 
Upvote 0
austin397 said:
Thanks hiker95!

That works for the example. I just needed a starting point.
Click to expand...

austin397,

Thanks for the feedback.

You are very welcome. Glad I could help.

I just need to modify the code a bit to fit with my real sheet which is much more complicated.
Click to expand...

Just let me know if you need additional help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Converting .txt file to .csv, splitting files and adding rows
2
Replies
10
Views
171
HongRu
HongRu
J
  • Solved
Speeding up VBA
Replies
9
Views
389
jvoss
J
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
222
Denny57
D
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
610
Ol Reliable
O
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
783
Davenil
D

Forum statistics

Threads
1,226,848
Messages
6,193,317
Members
453,790
Latest member
yassinosnoo1

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