0

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

I have a macro which checks if J & K are empty, and if they are it adds values from H & I.

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 conditions.

  1. If J is not blank, then compare all of the values from H with values from J and add missing values from J to the end of the list, wherever it might be, and put values from I to K.
    For example, if H30 value is missing from column J, I want H30 and I30 to be added to J&K column.

  2. If J is not blank, then compare all of the values from H with values from J. 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, sum I30 with K30.

My spreadsheet
My spreadsheet

Community
  • 1
  • 1
Xiggie
  • 1
  • 2
  • 1
    You only need one loop: Loop through column A, for each value use the [WorksheetFunction.CountIf method](https://learn.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