Filldown when don't know Column reference other than by Header Name in VBA Macros; Excel 2013, Windows 10

minddumps

New Member
Joined
Jun 29, 2011
Messages
16
Good day all,

I posted a much wider subject here: https://www.mrexcel.com/forum/excel...names-vba-macros-excel-2013-windows-10-a.html but am not getting much responses so I decided to break it down a bit with a few edits I've been working with.

I have a worksheet with requested data filled out by someone else and in which columns are not always in the same order... so I'm editing VBA to search for a column based on a header and then working through that way. The biggest challenge I have is that I'm a novice who researches, copies, pastes, and then play around with the code until I can get it to do what I want. Here's where I am at so far:

This is what I have working properly:
Code:
Private Sub STEP4_Separate_Name()
'
' STEP4_Separate_Name Macro
'
    '''''''''''''''''''''''''''''''''''''''''''''Insert 4 columns to right''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim rngNameHeader As Range
    Dim rngMyNewColumn As Range

    Set rngNameHeader = Range("A1:ZZ1").Find(What:="Name", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
    rngNameHeader.Offset(, 1).Resize(, 4).EntireColumn.Insert Shift:=xlToRight

    
    '''''''''''''''''''''''''''''''''''''''''''''Insert Proper formula to first column on the right'''''''''''''''''''''''''''''''''''''''''''''

                   
    rngNameHeader.Offset(1, 1).FormulaR1C1 = "=PROPER(RC[-1])" 'This works properly and inserts the formula in the first column after "Name" column and second row

:(This is where my sheet keeps error-ing out:
Code:
        Range(rngNameHeader.Offset(1, 1), rngNameHeader.Offset(, 1).Column & Cells(Rows.Count, 1).End(xlUp).Row).FillDown 'I've tried so many different things for this line to no avail.

     'NOTE: This is what my recorded macro shows, but the Column Name might not always be G'''
            'Range("G2").Select
            'ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
            'Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

:confused:Can anyone help me figure this piece of code out so that I can continue figuring out the rest?

BTW, someone had suggested pasting down using .EntireColumn: but that won't work because it adds too much data into the file... I don't want unnecessary rows having data added to them as this code is only part of a much larger workbook.:

:confused:Here's the rest of this section of code; I could really use the help checking my work to see if what I've written will work (since I can't get past the line above, I'm unable to see if this code below will work erg):

Code:
    '''''''''''''''''''''''''''''''''''''''''''''Select same column with formulas to paste values'''''''''''''''''''''''''''''''''''''''''''''
    rngNameHeader.Offset(, 1).EntireColumn.Select ' This should be the first column after "Name" that has all the formulas copied down each row
    Selection.Copy
    rngNameHeader.EntireColumn.Select 'This should be the "Name" column to paste the new "Proper" Names
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    rngNameHeader.Offset(, 1).EntireColumn.Select 'This should select the original "Name" Column, though the header will now be blank
    Application.CutCopyMode = False
    Application.DisplayAlerts = False

Then:
Code:
    '''''''''''''''''''''''''''''''''''''''''''''Seperate Names to concatenate to the columns to the right'''''''''''''''''''''''''''''''''''''''''''''
        Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True 'This is from the Text to Columns selection on the Data Tools Tab from the Data Menu Item
    Range(rngNameHeader).Select 'Select the Header box to add the text
    ActiveCell.FormulaR1C1 = "Last Name"
    Range(rngNameHeader.Offset(, 1)).Select 'Select the Header box in 1 column to the right to add the text
    ActiveCell.FormulaR1C1 = "First Name"
    Range(rngNameHeader.Offset(, 2)).Select 'Select the Header box in 2 columns to the right to add the text
    ActiveCell.FormulaR1C1 = "MI"
    Range(rngNameHeader.Offset(, 3)).Select 'Select the Header box in 3 columns to the right to add the text
    ActiveCell.FormulaR1C1 = "Full Name"
            Range(rngNameHeader.Offset(, 4)).Select 'Select the Header box in 4 columns to the right to add the text
            Application.CutCopyMode = False
            Selection.EntireColumn.Delete Shift:=xlToLeft 'Delete entire column 4 columns to the right
    Range(rngNameHeader.Offset(, 3)).Select 'Select the Header box in 3 columns to the right to add the text 'Column Header Full Name
    rngNameHeader.Offset(1, 3).FormulaR1C1.FormulaR1C1 = _
        "=CONCATENATE(RC[1],"" "",RC[-2],"" "",RC[-1],"" "",RC[-3])" 'I need to concatenate existing Column Header Row "Rank", Column Header "First Name", "MI", "Last Name"; RC will work for the newly created columns, but not for the Rank column--that will have to be searched for and placed in the formula
            Range("I2", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown '********************************This will follow code from above "Select same column with formuals to paste values section"
    Range(rngNameHeader.Offset(, 3)).EntireColumn.Select 'This is the column that the concatenate formula was written into
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    

End Sub

Any and all help is appreciated!! Thank you!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top