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

[minddumps]

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:

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:

        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

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

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):

    '''''''''''''''''''''''''''''''''''''''''''''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:

    '''''''''''''''''''''''''''''''''''''''''''''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]

Try replacing the problem line & the formula line with

    Lr = Cells(Rows.Count, rngNameHeader.Offset(, -1).Column).End(xlUp).Row
    rngNameHeader.Offset(1, 1).Resize(Lr - 1).FormulaR1C1 = "=PROPER(RC[-1])"

 

[minddumps]

Ahaaa... yes that helped tremendously and I was able to fix the rest of my formulas... Thank you!

One other question though... do you know how to find another column within concatenate? here's what I have but I'm given #NAME ? as the end result...

    Set rngRankHeader = Range("A1:ZZ1").Find(What:="Rank2", LookIn:=xlValues, _
                    MatchCase:=False, SearchFormat:=False) 'Find Rank Header 'LookAt:=xlWhole,
    
LR = Cells(Rows.Count, rngNameHeader.Offset(, -4).Column).End(xlUp).Row 'I need to concatenate existing Column Header Row "Rank", Column Header "First Name", "MI", "Last Name";
    rngNameHeader.Offset(1, 3).Resize(LR - 1).FormulaR1C1 = "=CONCATENATE(rngRankHeader,"" "",RC[-2],"" "",RC[-1],"" "",RC[-3])"

 

[Fluff]

Try

"=CONCATENATE(" & rngRankHeader.Address(1, 1, xlR1C1) & ","" "",RC[-2],"" "",RC[-1],"" "",RC[-3])"

 

[minddumps]

Thank you! I found a work around and went to try yours instead (much cleaner than my workaround hahaha) and another section of my code that has always worked before is giving me an error ugh.... once I get that fixed I'll come back here and try that and post for anyone else who is looking for a similar solution.

Thanks again

 

[Fluff]

You're welcome

 

[minddumps]

Hmm, I got it all working again and tried your sample code in place of mine, but it didn't work for me... it took the header cell text and put that in front of all the other rows vs. using that columns' text in the appropriate row.

For anyone else that is searching however here is what I came up with that is working the way I want it to (I even through in a replace double spaces with one space instead of using another if statement to fill the concat:

Private Sub STEP4_Separate_Name()
'
'''''''''''''''''''''''''''''''''''''''''''''Insert 4 columns to right''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim rngNameHeader As Range
    Dim rngRankHeader As Range
    Dim rngMyNewColumn As Range

    Set rngNameHeader = Range("A1:ZZ1").Find(What:="Name", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
                    
                        If rngNameHeader Is Nothing Then
                        msgbox "A column with the header 'Name' was not found; recheck your column names to ensure the 'Name' column does have the header 'Name'.", vbExclamation
                        Exit Sub
                        End If
                    
    rngNameHeader.Offset(, 1).Resize(, 4).EntireColumn.Insert Shift:=xlToRight

    
'''''''''''''''''''''''''''''''''''''''''''''Insert Proper formula to first column on the right'''''''''''''''''''''''''''''''''''''''''''''
    LR = Cells(Rows.Count, rngNameHeader.Offset(, -1).Column).End(xlUp).Row
    rngNameHeader.Offset(1, 1).Resize(LR - 1).FormulaR1C1 = "=PROPER(RC[-1])"
             
'''''''''''''''''''''''''''''''''''''''''''''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.EntireColumn.Select 'This should select the original "Name" Column, though the header will now be blank
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    
'''''''''''''''''''''''''''''''''''''''''''''Seperate Names'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This first part is original Name column, select row 1
        
        Selection.TextToColumns Destination:=rngNameHeader, 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

rngNameHeader.Select 'Select the Header box to add the text
    ActiveCell.FormulaR1C1 = "Last Name"
    rngNameHeader.Offset(, 1).Select 'Select the Header box in 1 column to the right to add the text
    ActiveCell.FormulaR1C1 = "First Name"
    rngNameHeader.Offset(, 2).Select 'Select the Header box in 2 columns to the right to add the text
    ActiveCell.FormulaR1C1 = "MI"
    rngNameHeader.Offset(, 3).Select 'Select the Header box in 3 columns to the right to add the text
    ActiveCell.FormulaR1C1 = "Full Name"
            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
    rngNameHeader.Offset(, 3).Select 'Select the Header box in 3 columns to the right to add the text 'Column Header Full Name
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''to concatenate to the columns to the right'''''''''''''''''''''''''''''''''''''''
    Set rngRankHeader = Range("A1:ZZ1").Find(What:="Rank", LookAt:=xlPart, _
                    MatchCase:=False, SearchFormat:=False) 'Find Rank Header 'LookIn:=xlValues,
                        
                        If rngRankHeader Is Nothing Then
                        msgbox "A column with the header 'Rank' was not found; recheck your column names to ensure the 'Rank' column does have the header 'Rank'.", vbExclamation
                        Exit Sub
                        End If
                        
    LR = Cells(Rows.Count, rngNameHeader.Offset(, -4).Column).End(xlUp).Row 'I need to concatenate existing Column Header Row "Rank", Column Header "First Name", "MI", "Last Name";
    rngNameHeader.Offset(1, 3).Resize(LR - 1).FormulaR1C1 = _
    "=CONCATENATE(" & Cells(2, rngRankHeader.Column).Address(0, 0) & ","" "",RC[-2],"" "",RC[-1],"" "",RC[-3])" '********************************This will follow code from above; Select same column with formuals to paste values section"
                
                '''''''''''''''''Another option given as an example is:''''''''''''''''
                '     "=CONCATENATE(" & rngRankHeader.Address(1, 1, xlR2C1) & ","" "",RC[-2],"" "",RC[-1],"" "",RC[-3])" 'This didn't work for me, it used the header name for the beginning of all names instead of the appropriate matching row's text

                
        rngNameHeader.Offset(, 3).EntireColumn.Select
        Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    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

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''replace double spaces with a single space'''''''''''''''''''''''''''''''''''''''    
        rngNameHeader.Offset(, 3).EntireColumn.Select
        Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

 

[minddumps]

Good morning Fluff, I thought I had found a work-a-round, but it turns out the same cell's data was being repeated also. Do you have any other ideas how to concatenate using a range name?

My explanation of this section is here:

Hello All,

I am receiving incorrect data with this piece of my VBA.

            Set rngRankHeader = Range("A1:ZZ1").Find(What:="Rank", LookAt:=xlPart, _
                    MatchCase:=False, SearchFormat:=False) 'Find Rank Header 'LookIn:=xlValues,
                        
                        If rngRankHeader Is Nothing Then
                        MsgBox "A column with the header 'Rank' was not  found; recheck your column names to ensure the 'Rank' column does have  the header 'Rank'.", vbExclamation
                        Exit Sub
                        End If
                        
            LR = Cells(Rows.Count, rngNameHeader.Offset(,  -4).Column).End(xlUp).Row 'I need to concatenate existing Column Header  Row "Rank", Column Header "First Name", "MI", "Last Name";
            rngNameHeader.Offset(1, 3).Resize(LR - 1).FormulaR1C1 = _
            "=CONCATENATE(" & Cells(2,  rngRankHeader.Column).Address(0, 0) & ","" "",RC[-2],"" "",RC[-1],""  "",RC[-3])" '********************************This will follow code from  above; Select same column with formuals to paste values section"
                
            rngNameHeader.Offset(, 3).EntireColumn.Select
            Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

The piece that's incorrect is

"& Cells(2,   rngRankHeader.Column).Address(0, 0) &

because it is returning a particular cell's data in the selected column and repeating it throughout every row vs giving me the column's cell's data within the corresponding row. I also tried using

&  rngRankHeader.Address(1, 1, xlR2C1) &

and that didn't work either.

For example:

abc	def	=abcdef (as it should)
abc	ghi	=abcdef (pulling from 1st cell instead of ghi)
abc	jkl	=abcdef (pulling from 1st cell instead of jkl)

<tbody>
</tbody>

Is anyone able to correct this for me?

https://www.mrexcel.com/forum/excel...-corresponding-row-vba-excel.html#post5172784

You're welcome

 

[Fluff]

This

"=CONCATENATE(" & rngRankHeader.Address(1, 1, xlR2C1)

should be xlR1C1

 

[minddumps]

hmmm... ahh ok... thanks again!! :D