0

I am very new to VBA and I am struggling with comparing two columns and adding missing values from H&I to J&K, while keeping the rest of my macro intact.

So far I have a macro which checks if J&K are empty, and if they are empty it adds values from H&I to them, see below:

Sub Total()
Application.ScreenUpdating = False
Dim c  As Range
    For Each c In Range("J23:J32" & Cells(Rows.Count, "J").End(xlUp).Row)
        If c.Value = "" Then c.Value = c.Offset(, -2).Value
    Next
    For Each c In Range("K23:K32" & Cells(Rows.Count, "K").End(xlUp).Row)
    If c.Value = "" Then c.Value = c.Offset(, -2).Value
    Next
Application.ScreenUpdating = True
End Sub

I want to add two additional conditions:

  1. If J is not blank, then compare all of the values from H with values from J and add missing values to J to the end of the list, wherever it might be, and put values from I from to K.

So, for example, if H30 value is missing from column J, I want H30 and I30 to be added to J&K column.

  1. If J is not blank, then compare all of the values from H with values from J, and if the values match, then SUM the value from column I with value from column K.

For example, if H30 is present anywhere in column J, I want to sum I30 with K30.

Unfortunately this is way above my skill level, so I am humbly asking for assistance. At this point I am unsure if it's possible at all without completely changing existing logic.

Thank you very much in advance!!

Cheers

My spreadsheet right now My spreadsheet right now

Pᴇʜ
  • 53,845
  • 9
  • 46
  • 68
Xiggie
  • 1
  • 2
  • 1
    You only need one loop: Loop through column A, for each value use the [WorksheetFunction.CountIf method](https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.countif) on column B. If that count is `=0` the value does not exist in B and needs to be added. – Pᴇʜ Mar 07 '22 at 13:31
  • HI & Thanks! However my problem is, I don't know how to add the missing value to destination column to the end of the list – Xiggie Mar 07 '22 at 13:38
  • Sorry, cannot check suggested edits since I lack reputation – Xiggie Mar 07 '22 at 13:40
  • 1
    Use [Find Last Row in a Column](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) to find the last used row in column B. Then use something like `Cell(LastRow + 1, "B").Value = TheValueYouLikeToAdd`. – Pᴇʜ Mar 07 '22 at 13:44
  • Thank you for the suggestion! This is beyond my comprehension unfortunatel. I understand the parts yet I cannot build a working macro to fulfill both my conditions. Back to the drawing board I go! – Xiggie Mar 07 '22 at 17:29

0 Answers0