Need help with VBA code (NEW TO VBA)

[JJ EXCEL1234]

I need help writing code to help with my job. Basically, this is what I need it to do:

1. Sheet1 contains a list of unique numbers in column B, and columns in the same row containing no data
2. Sheet2 contains prior data, which includes the same unique numbers and inputted data on the same row
3. I need a script to go through the unique numbers on Sheet1 and search Sheet2 for the indenticle unique number
4. If an identical number is not found, the script continues to the next row until no rows are left
5. If an identical number is found, it will copy data from the columns of that row in sheet2 to sheet 1, but it will not overwrite data that is already in sheet1
6. (so if the script finds a match to B3 from sheet1, on sheet2, it will copy data from sheet2 from C3, D3, etc and paste the values to sheet 1, but only for data boxes blank on sheet1.

Thats it! Hopefully it can do this for about 500 rows so I don't need to re-input data I already once input in a past assignment. Does this make sense?

Thanks in advance for the help. I started to learn VBA to try and solve this task. Any help would be much appreciated. I am very new to VBA and need all the help I can get.

JJ

 

[Peter_SSs]

Test in a copy of your workbook.

I have assumed ..
- Headings in row 1 of both sheets
- Data starts in column A of both sheets
- Same number of columns of data in both sheets (rows can be different)
- No formulas in Sheet1, or at least none that need to be retained.

Sub Update_Data()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, cols As Long, rw As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  b = Sheets("Sheet2").UsedRange.Value
  cols = UBound(b, 2)
  For i = 2 To UBound(b)
    d(b(i, 2)) = i
  Next i
  With Sheets("Sheet1")
    a = .UsedRange.Resize(, cols).Value
    For i = 2 To UBound(a)
      rw = d(a(i, 2))
      If rw > 1 Then
        For j = 1 To cols
          If IsEmpty(a(i, j)) Then a(i, j) = b(rw, j)
        Next j
      End If
    Next i
    .Range("A1").Resize(UBound(a), cols).Value = a
  End With
End Sub

 

[JJ EXCEL1234]

Test in a copy of your workbook.

I have assumed ..
- Headings in row 1 of both sheets
- Data starts in column A of both sheets
- Same number of columns of data in both sheets (rows can be different)
- No formulas in Sheet1, or at least none that need to be retained.

Sub Update_Data()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, cols As Long, rw As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  b = Sheets("Sheet2").UsedRange.Value
  cols = UBound(b, 2)
  For i = 2 To UBound(b)
    d(b(i, 2)) = i
  Next i
  With Sheets("Sheet1")
    a = .UsedRange.Resize(, cols).Value
    For i = 2 To UBound(a)
      rw = d(a(i, 2))
      If rw > 1 Then
        For j = 1 To cols
          If IsEmpty(a(i, j)) Then a(i, j) = b(rw, j)
        Next j
      End If
    Next i
    .Range("A1").Resize(UBound(a), cols).Value = a
  End With
End Sub

Thank you for helping! I will try it first thing tomorrow morning. Do you think you could do one more favor for me please? Could you notate your code with comments so I can better understand what each line does? Thanks again.

JJ

 

[Peter_SSs]

Could you notate your code with comments so I can better understand what each line does?

Let's first see if it works or at least goes close.

 

[JJ EXCEL1234]

Let's first see if it works or at least goes close.

Peter,

I have tried your code and it is not working. It appears to get caught up on "Set d = CreateObject("Scripting.Dictionary"). I think I should've told you that I am using excel for mac 2016... I did a quick google search on the error, and apparently mac excel does not have a scripting runtime library (see link). Is there another way we can give this a shot?


JJ

 

[Peter_SSs]

I think I should've told you that I am using excel for mac 2016...

Although there are quite a few Mac users who help on the forum, by far the majority are not so, yes, it would be prudent of you to mention that up front in the future.

Give this a try. It has more looping but with only about 500 rows it shouldn't matter.

Sub Update_Data_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, cols As Long, rw As Long, ubb As Long
  
  b = Sheets("Sheet2").UsedRange.Value
  ubb = UBound(b, 1)
  cols = UBound(b, 2)
  With Sheets("Sheet1")
    a = .UsedRange.Resize(, cols).Value
    For i = 2 To UBound(a)
      rw = 0
      k = 1
      Do
        k = k + 1
        If b(k, 2) = a(i, 2) Then rw = k
      Loop Until rw > 0 Or k = ubb
      If rw > 1 Then
        For j = 1 To cols
          If IsEmpty(a(i, j)) Then a(i, j) = b(rw, j)
        Next j
      End If
    Next i
    .Range("A1").Resize(UBound(a), cols).Value = a
  End With
End Sub