Populate sheet based on row and column value

[Muimdac13]

Hi,

I've found good answers to questions others have asked here in the past, so figured I why not try.
I'm new to VBA and macros, I normally can do what I want with formulas but not this time.

I currently have a button that asks for two separate text strings:
A Name and A Type.

What I want to do is match the Name with one of the names in the first column.
If a match is found I want it to fill that row based on the column headings (search all column headings for Type).
Any cell in the row with a column heading that contains the Type within its string, will be filled in with the entered Name & Type strings joined together.

Example:

[TABLE="width: 362"]
<tbody>[TR]
[TD][/TD]
[TD]AaBaCaDa[/TD]
[TD]AbBbCbDb[/TD]
[TD]AaBbCbDb[/TD]
[TD]AbBaCaDb[/TD]
[/TR]
[TR]
[TD]ALAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALGT[/TD]
[TD]ALGTBa[/TD]
[TD][/TD]
[TD][/TD]
[TD]ALGTBa[/TD]
[/TR]
[TR]
[TD]CAP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZAP[/TD]
[TD][/TD]
[TD]ZAPAb[/TD]
[TD][/TD]
[TD]ZAPAb[/TD]
[/TR]
</tbody>[/TABLE]


I'm not worried about overwriting things when a new type is entered for my use that won't be a problem.

Any help on what this code should look like would be really appreciated, I haven't been having much luck.

 

[HHoward1984]

Hi

I may be misreading this but i don't think you need a Marco. I think what you need is an index match formula. What the formula will do is basically a vlookup and an Hlookup at the same time so if you were looking for the product of ROW "ZAP" and COLUMN "AbBaCaDb" it would return "ZAPAb"

You can find loads of help on Index and Match through Google.

Like i said i am not 100% sure what you are trying to achieve so if this is wrong please accept my apologies

 

[Muimdac13]

Thank you for the quick reply I looked up some index match tutorials but I don't think index match will work for this task.

I'm basically trying to make a user friendly way to fill in a simple data base.

So the user would enter ZAP under name and Ab under type and then the data base will automatically fill in all cells in the ZAP row where the column header contains Ab.

 

[Fluff]

Hi & welcome to the board.
How about this

Sub FindNameType()

   Dim AName As String
   Dim Atype As String
   Dim Cnt As Long
   Dim Qty As Long
   Dim FndN As Range
   Dim FndT As Range
   
   AName = InputBox("Please enter a name")
   Atype = InputBox("Please enter a type")
   Set FndT = Range("A1")
   Set FndN = Columns(1).Find(AName, , , xlWhole, , , True, , False)
   If FndN Is Nothing Then
      MsgBox AName & " not found"
      Exit Sub
   End If
   Qty = WorksheetFunction.CountIf(Rows(1), "*" & Atype & "*")
   For Cnt = 1 To Qty
      Set FndT = Rows(1).Find(Atype, FndT, , xlPart, , , True, , False)
      Cells(FndN.Row, FndT.Column) = AName & Atype
   Next Cnt
   
End Sub

 

[Muimdac13]

Fluff,

THANK YOU!
I knew it could be done just didn't have the faintest idea how.
I'll be looking this over to try to understand the code.

Because I live in formulas, I had resorted to what I knew was an inefficient solution.
A table full of double if statements and then a simple copy paste button macro, this is so much better.

 

[Fluff]

Glad to help & thanks for the feedback.