VBA Check if Cell is Empty & Add to Database Sheet

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
160
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)

RefMerged NameName 1Name 2AddrCodeQuantityCategory
001Sean SmithSeanSmithLA1115000Included
002Ian GropIanGropSA1254500Excluded
003Lea MasonLeaMasonAM1164000For Review
004Tricia JavierTriciaJavierMT1023000
005Den BertheDenBertheBE1152500Excluded
006Mae LimMaeLimAB1362000
007Oscar ChardeOscarChardeCH1401000

<tbody>
</tbody>

*Database Excel Sheet (Database tab)

AccountData 1Merged NameData 2AddrxCodexxxxxCategory
Sean SmithLA111Included
Ian GropSA125Excluded
Lea MasonAM116For Review
Den BertheBE115Excluded

<tbody>
</tbody>

Any help will be much appreciated.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,991
Office Version
365
Platform
Windows
How about
Code:
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

Board Regular
Joined
Sep 19, 2017
Messages
160
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,991
Office Version
365
Platform
Windows
How about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,991
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top