VBA Check if Cell is Empty & Add to Database Sheet

[unknownymous]

Hi Excel Gurus,

I'm working on an macro project and almost done. I'm just having a problem on this: I have 2 excel sheet, one is working file and the other is database. What the macro needs to do is to check the cell with blank category (in working file) and add below column data in the database (next blank cell).

1) Merged Name
2) Addr
3) Code


*Working File Excel Sheet (Working tab)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref[/TD]
[TD]Merged Name[/TD]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[TD]Addr[/TD]
[TD]Code[/TD]
[TD]Quantity[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Sean Smith[/TD]
[TD]Sean[/TD]
[TD]Smith[/TD]
[TD]LA[/TD]
[TD]111[/TD]
[TD]5000[/TD]
[TD]Included[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Ian Grop[/TD]
[TD]Ian[/TD]
[TD]Grop[/TD]
[TD]SA[/TD]
[TD]125[/TD]
[TD]4500[/TD]
[TD]Excluded[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Lea Mason[/TD]
[TD]Lea[/TD]
[TD]Mason[/TD]
[TD]AM[/TD]
[TD]116[/TD]
[TD]4000[/TD]
[TD]For Review[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]Tricia Javier[/TD]
[TD]Tricia[/TD]
[TD]Javier[/TD]
[TD]MT[/TD]
[TD]102[/TD]
[TD]3000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]Den Berthe[/TD]
[TD]Den[/TD]
[TD]Berthe[/TD]
[TD]BE[/TD]
[TD]115[/TD]
[TD]2500[/TD]
[TD]Excluded[/TD]
[/TR]
[TR]
[TD]006[/TD]
[TD]Mae Lim[/TD]
[TD]Mae[/TD]
[TD]Lim[/TD]
[TD]AB[/TD]
[TD]136[/TD]
[TD]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]007[/TD]
[TD]Oscar Charde[/TD]
[TD]Oscar[/TD]
[TD]Charde[/TD]
[TD]CH[/TD]
[TD]140[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

*Database Excel Sheet (Database tab)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Data 1[/TD]
[TD]Merged Name[/TD]
[TD]Data 2[/TD]
[TD]Addr[/TD]
[TD]x[/TD]
[TD]Code[/TD]
[TD]xx[/TD]
[TD]xxx[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sean Smith[/TD]
[TD][/TD]
[TD]LA[/TD]
[TD][/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD]Included[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ian Grop[/TD]
[TD][/TD]
[TD]SA[/TD]
[TD][/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[TD]Excluded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lea Mason[/TD]
[TD][/TD]
[TD]AM[/TD]
[TD][/TD]
[TD]116[/TD]
[TD][/TD]
[TD][/TD]
[TD]For Review[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Den Berthe[/TD]
[TD][/TD]
[TD]BE[/TD]
[TD][/TD]
[TD]115[/TD]
[TD][/TD]
[TD][/TD]
[TD]Excluded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help will be much appreciated.

 

[Fluff]

How about

Sub unknownymous()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         v = Join(Application.Index(Cl.Resize(, 5).Value, 1, Array(1, 3, 5)), "|")
         .Item(v) = Cl.Offset(, 7).Value
      Next Cl
      For Each Cl In Ws2.Range("B2", Ws2.Range("B" & Rows.Count).End(xlUp))
         v = Join(Application.Index(Cl.Resize(, 5).Value, 1, Array(1, 4, 5)), "|")
         Cl.Offset(, 6).Value = .Item(v)
      Next Cl
   End With
End Sub

 

[unknownymous]

This is amazing Fluff.

One thing, what if I want to add the Quantity from Working File and put it in the column next to Code in Database File?

 

[Fluff]

How about

Sub unknownymous()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
   Dim v As String
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("B2", Ws2.Range("B" & Rows.Count).End(xlUp))
         v = Join(Application.Index(Cl.Resize(, 5).Value, 1, Array(1, 4, 5)), "|")
         .Item(v) = .Item(v) + Cl.Offset(, 5).Value
      Next Cl
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         v = Join(Application.Index(Cl.Resize(, 5).Value, 1, Array(1, 3, 5)), "|")
         Cl.Offset(, 5).Value = .Item(v)
      Next Cl
   End With
End Sub

 

[unknownymous]

This is great! Thanks a lot Fluff!

 

[Fluff]

You're welcome & thanks for the feedback