Solved

Get rid of selecting hard-coded cell

Posted on 2016-09-01
8
76 Views
Last Modified: 2016-09-06
Basically, this macro adds a new column to the left of Project Title column. Then, it adds  a formula to the new column.

Originally, I recorded it. I need help towards the end. Change it so it doesn't matter what cell, regardless how many rows the sheet has. Right now, it uses C1770.

Sub Add_Col_L()
'
' Add_Col_L Macro
'
'
    Cells.Find(What:="Project Title", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "L"
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 20
    Selection.NumberFormat = "General"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[1])"
    Range("C2").Select
    Selection.Copy
    Range("D3").Select
    Selection.End(xlDown).Select
    ' Fix: Change below to Go 1 cell left instead of specific cell
    Range("C1770").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("C3:C1770").Select
    Range("C1770").Activate
    ActiveSheet.Paste
End Sub

Open in new window

0
Comment
Question by:NVIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 20
ID: 41780706
one way would be to put the value in a Name (as opposed to defining a range for the Name)
0
 
LVL 24

Author Comment

by:NVIT
ID: 41780721
Hi Crystal,

Would you please give an example?
0
 
LVL 20
ID: 41780728
sure -- Formulas ribbon tab, Name Manager, New... command button

Name: MyValue (obviously you want to name this better)
Scope: Workbook (or change to specific sheet)
Refers to: =999 (or whatever value you want)

then in a cell, you can use:
=MyValue+3
(or whatever is your formula)

optionally, you can Name the cell, if you want to keep the value in the sheet, and as columns or rows are added, the reference should adjust -- as should formulas that refer to a cell address without dollar signs ($) ... but your code is specifying a particular address which is not adjusted

to Name a cell:
1. select the cell
2. in the Name box that shows the address, type the name, starting with a letter, without space or special characters and then press ENTER
3. you can then use this Name in formulas instead of a cell reference
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 24

Author Comment

by:NVIT
ID: 41780744
Seems like your solution is giving a formula, which is not what I need. Correct me if I'm wrong.

I need a way to move the cell to the left vs. picking the cell that was recorded.
0
 
LVL 20
ID: 41780765
>"move the cell to the left vs. picking the cell that was recorded"

perhaps before you start recording, set "Use Relative References" on the developer ribbon

to get the last row and column:
   With xlWs
      nLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row  'xlUp=-4162
      nLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column 'xlToLeft=-4159
   End With

Open in new window

WHERE
xlWs is a worksheet object -- ie:Activeworkbook.sheets(1) -- or sheets("sheetname")
nLastRow and nLastCol are dimensioned as Long
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41781021
See if this is what you are trying to achieve.

Sub Add_Col_L()
Dim c As Long, lr As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
c = Cells.Find(What:="Project Title", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Column
Columns(c).Insert

Cells(1, c).FormulaR1C1 = "L"

With Columns(c)
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .ColumnWidth = 20
    .NumberFormat = "General"
End With
    Range(Cells(2, c), Cells(lr, c)).FormulaR1C1 = "=LEFT(RC[1])"
End Sub

Open in new window

0
 
LVL 24

Author Closing Comment

by:NVIT
ID: 41786675
Hi Subohd... This works great! Thank you.
Have a nice day/night.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41786691
You're welcome. Glad to help.
Thanks and same to you.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question