Thread: VBA - Input Value If Column Number Equals "x" within Non-Contiguous Named Range

Hi all,

First post as a newbie to VBA and Excel - have found these forums greatly useful in the past and am severely at a loss for how to accomplish the task at hand. Essentially, I want to create a macro that will auto-populate a predefined non-contiguous range of cells with a value that is based on another cell's value. The value that is populated depends on the cell's column number, which corresponds to a user inputted table.

I've attached a sample worksheet (the yellow cells are the predefined non-contiguous range of cells) - I do need a VBA solution, rather than formulas within the cells, because part of my macro will determine a unique placement of the non-contiguous range.



I know I can use a named range to address the non-contiguous range of cells in my macro. What I think might work is to identify the column number for each cell within the named range and insert the corresponding value in the user input table (perhaps using offsets?) I just don't know where to start - any help would be greatly appreciated!

greenexcel12345,

Welcome to the forum. Attached is a modified version of your sample workbook. It contains a button named "Update Table" which is assigned to the following macro:

Sub UpdateTableMacro_for_greenexcel12345()
    
    Const TableRange As String = "D5:I10"
    Const UserInput As String = "C14:D19"
    
    Dim rngTable As Range: Set rngTable = ActiveSheet.Range(TableRange)
    Dim arrInput() As Variant: arrInput = ActiveSheet.Range(UserInput).Value
    Dim i As Long
    
    Dim TableCell As Range
    For Each TableCell In rngTable
        If TableCell.Interior.ColorIndex = 36 Then
            For i = 1 To UBound(arrInput, 1)
                If Cells(rngTable.Row - 1, TableCell.Column).Value = arrInput(i, 1) Then
                    TableCell.Value = arrInput(i, 2)
                    Exit For
                End If
            Next i
        End If
    Next TableCell
    
End Sub

Hope that helps,
~tigeravatar