Send Data To First Blank Cell Of Named Range

yegarboy · Sep 29th 2015

Greetings my friends,

Is it possible to send data to the first blank cell of a Named Range and then continue filling down? There will be 3-5 named ranges.

Example:

Named Range "MyRange" has 10 cells. A1 To A10. I need to send data to the first cell "A1" and fill down until "A10" if needed. I am using a search Macro to grab the data..And it works fine except I need to send data to 3 or more Named Ranges on the same sheet.

Example 2:

My Macro looks for a Name in say Column D. If the name is found it looks for a singe letter in the same row Column B. Then it copies that name into a named range and fills it down.

I hope this makes sense to someone

Smallman · Sep 29th 2015

Re: Send Data To First Blank Cell Of Named Range

Hi Yegarboy

Can we see a workbook with some sample data and the final output?

Take care

Smallman

yegarboy · Sep 29th 2015

Re: Send Data To First Blank Cell Of Named Range

Here is a sample good sir.

forum.ozgrid.com/index.php?attachment/67054/

Smallman · Sep 29th 2015

Re: Send Data To First Blank Cell Of Named Range

Here you go. Might want to check the spelling of Hasbo (Hasbro was in M3).

I have completed table A for you. Go forth and finish Table B & C.

Take care

Smallman

yegarboy · Sep 29th 2015

Re: Send Data To First Blank Cell Of Named Range

Thanks for the fast response. However I meant to add I needed this in VBA. Sorry about that.

Smallman · Sep 29th 2015

Re: Send Data To First Blank Cell Of Named Range

Hi Yegarboy

I never use VBA when I believe formula will do the same job. Sorry about that.

I did manage to improve the formula for you though. Workbook attached.

Smallman

yegarboy · Sep 30th 2015

Re: Send Data To First Blank Cell Of Named Range

Thank you for all of your help Smallman Unfortunately tho a Macro is needed.

**Trebor76** · Sep 30th 2015

Re: Send Data To First Blank Cell Of Named Range

Though I totally agree with Smallman - here's macro to do the job for you:

Code

Option Explicit


Sub Macro3() 
  
    Dim varMyRange As Variant
    Dim rngMyCell As Range
    Dim lngMyRowCatA As Long, lngMyRowCatB As Long, lngMyRowCatC As Long
    
    If Len(Range("M3")) = 0 Then
        MsgBox "There is no search string in cell M3!!", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    'Clear existing search results
    For Each varMyRange In Array("H6:J12", "H15:J21", "H24:J30")
        Range(varMyRange).ClearContents
    Next varMyRange
    
    For Each rngMyCell In Range("D6:D15")
        If StrConv(rngMyCell, vbProperCase) = StrConv(Range("M3"), vbProperCase) Then
            Select Case StrConv(Range("C" & rngMyCell.Row), vbUpperCase)
                Case Is = "A"
                    If lngMyRowCatA = 0 Then
                        lngMyRowCatA = 6
                    Else
                        lngMyRowCatA = lngMyRowCatA + 1
                    End If
                    Range("H" & lngMyRowCatA).Value = Range("A" & rngMyCell.Row).Value
                    Range("I" & lngMyRowCatA).Value = Range("B" & rngMyCell.Row).Value
                    Range("J" & lngMyRowCatA).Value = Range("C" & rngMyCell.Row).Value
                Case Is = "B"
                    If lngMyRowCatB = 0 Then
                        lngMyRowCatB = 15
                    Else
                        lngMyRowCatB = lngMyRowCatB + 1
                    End If
                    Range("H" & lngMyRowCatB).Value = Range("A" & rngMyCell.Row).Value
                    Range("I" & lngMyRowCatB).Value = Range("B" & rngMyCell.Row).Value
                    Range("J" & lngMyRowCatB).Value = Range("C" & rngMyCell.Row).Value
                Case Is = "C"
                    If lngMyRowCatC = 0 Then
                        lngMyRowCatC = 24
                    Else
                        lngMyRowCatC = lngMyRowCatC + 1
                    End If
                    Range("H" & lngMyRowCatC).Value = Range("A" & rngMyCell.Row).Value
                    Range("I" & lngMyRowCatC).Value = Range("B" & rngMyCell.Row).Value
                    Range("J" & lngMyRowCatC).Value = Range("C" & rngMyCell.Row).Value
            End Select
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Done", vbExclamation
End Sub

Display More

Note 'Matel' is also misspelt.

Regards,

Robert

yegarboy · Sep 30th 2015

Re: Send Data To First Blank Cell Of Named Range

Thank you so much. This is just what I needed.

-Cheers!

**Trebor76** · Sep 30th 2015

Re: Send Data To First Blank Cell Of Named Range

Thanks for letting us know and you're welcome

Send Data To First Blank Cell Of Named Range

Participate now!

Share