Good day to you. I'm trying to revamp a bit of VBA code based on unknown column locations, known only by their header names.
This first bit of code is working properly as it should:
This is where I'm struggling; I've added notes to what each column section should reference:
I'd greatly appreciate some help on figuring out how to find unknown columns and tell excel how to find them.
This first bit of code is working properly as it should:
Code:
Private Sub STEP4_Separate_Name()
'
'''''''''''''''''''''''''''''''''''''''''''''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
'You'll need to check the named range doesn't exist first.
ThisWorkbook.Names.Add Name:="MyNewRange", _
RefersTo:="='" & rngNameHeader.Parent.Name & "'!" & _
rngNameHeader.Offset(1, 1).Address
Set rngMyNewColumn = Range("MyNewRange")
' MsgBox rngMyNewColumn.Address
'''''''''''''''''''''''''''''''''''''''''''''Insert "Proper" formula to first column on the right''''''''''''''''''''''''''''''''''''''''''''''''''''
'NOTE: This is what my recorded macro shows, but the Column to select might not always be G'''
'Range("G2").Select
'ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
'Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
rngMyNewColumn.FormulaR1C1 = "=PROPER(RC[-1])" 'This works properly and inserts the formula in the first column after "Name" column and second row
This is where I'm struggling; I've added notes to what each column section should reference:
Code:
Range(rngMyNewColumn, [Code to find Column Letter here] & Cells(Rows.Count, 1).End(xlUp).Row).FillDown 'I've tried so many different things for this line to no avail.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''Select same column with formulas to paste values'''''''''''''''''''''''''''''''''''''''''''''
Columns("G:G").Select ' This should be the first column after "Name" that has all the formulas copied down each row
Selection.Copy
Columns("F:F").Select 'This should be the "Name" column to paste the new "Proper" Names
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select 'This should select the original "Name" Column, though the header will now be blank
Application.CutCopyMode = False
Application.DisplayAlerts = False
'''''''''''''''''''''''''''''''''''''''''''''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("F1").Select
ActiveCell.FormulaR1C1 = "Last Name"
Range("G1").Select
ActiveCell.FormulaR1C1 = "First Name"
Range("H1").Select
ActiveCell.FormulaR1C1 = "MI"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Full Name"
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("I2").Select 'Column Header Full Name
ActiveCell.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
Columns("I:I").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
End Sub
I'd greatly appreciate some help on figuring out how to find unknown columns and tell excel how to find them.