VBA Turn values in one column based on similarities in another

[oliviar]

Hi Guys,
I tried this last week but no luck.
I'm going to try a better explanation.

I have two category columns and one value column.
Category column A will always be filled. But category B will only be filled for one of the cells. I want Column B to be full of whatever value is represented anywhere for the same value as in Category A.

So if I have:

Cat A | Cat B | Value
Apples | Fruit | $2
Apples | ___ | $5
Apples | ___ | $3

I want a macro that will make it:

Cat A | Cat B | Value
Apples | Fruit | $2
Apples | Fruit | $5
Apples | Fruit | $3

 

[Trebor76]

Hi oliviar,

Try this where the assumptions is that the data starts from cell B2:

Option Explicit
Sub Macro1()

    Dim rngCell As Range, _
        rngDataSet As Range
    Dim varItem As Variant
    
    varItem = Range("B2").Value
    
    Set rngDataSet = Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    
    Application.ScreenUpdating = False
    
    For Each rngCell In rngDataSet
    
        If Len(rngCell.Value) = 0 Then
            rngCell.Value = varItem
        Else
            varItem = rngCell.Value
        End If
        
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert

 

[oliviar]

This looks pretty exciting. I'll use it as soon as excel wakes up from another macro I'm running.

Would you mind giving me a little bit of an explanation of what Len = 0 is doing?
I try to parse all the code I'm giving, but that one stumps me

 

[Trebor76]

Would you mind giving me a little bit of an explanation of what Len = 0 is doing

Len is the function used to test the length of characters in a cell, so if there's no characters (i.e. the cell is null) the Len will return 0 or else it will return the number of characters in a cell.

If my macro solves the issue you should put a remark on your earlier post that it was solved by this thread in case someone else spends time providing a solution when one has been found.

Robert

 

[oliviar]

Hi Robert,
I finally got to use your code today and it works perfectly. Pretty speedy.

I can now use this for so many other things too!

I'm still not quite sure how its doing what its doing, but I'll work on it.
Thanks so much.

 

[Trebor76]

You're welcome