Solved

Macro Code Needs Revision

Posted on 2013-01-19
7
215 Views
Last Modified: 2013-01-19
Hello Experts,

I am using this code...

Sub ClearMeasuringUp()
    If MsgBox("You are about to clear the entire worksheet - Are you sure you want to proceed?", vbYesNo) = vbYes Then
        ActiveSheet.Range("B28:D28,F28:H28,J28:L28,P28").ClearContents
        Application.Goto Reference:="Diameter"
        Selection.ClearContents
    End If
End Sub

Open in new window


After the "Selection.ClearContents" - I need the code to go to cell N7 and type the word "Diameter".

Thank you in advance for your help!

~ Geekamo
0
Comment
Question by:Geekamo
[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
  • 4
  • 3
7 Comments
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38796496
Sub ClearMeasuringUp()
    If MsgBox("You are about to clear the entire worksheet - Are you sure you want to proceed?", vbYesNo) = vbYes Then
        ActiveSheet.Range("B28:D28,F28:H28,J28:L28,P28").ClearContents
        Activesheet.Cells(7, 14).Value = "Diameter"
        Application.Goto Reference:="Diameter"
        Selection.ClearContents
    End If
End Sub
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38797693
@ MartinLiss,

I am now using this code...

Sub ClearMeasuringUp()
    If MsgBox("You are about to clear the entire worksheet - Are you sure you want to proceed?", vbYesNo) = vbYes Then
        ActiveSheet.Range("B28:D28,F28:H28,J28:L28,P28").ClearContents
        ActiveSheet.Cells(27, 14).Value = "Diameter"
        Application.Goto Reference:="Diameter"
        Selection.ClearContents
    End If
End Sub

My original cell reference in my post wasn't correct.  So I see that 27, represents row #, and 14 represents the 14th column.  Mind you I know nothing about code, but can't you just reference the cell via it's cell name N27?

~ Geekamo
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38797725
Yes, you can

ActiveSheet.Range("A27").Value = "Diameter"

They're both the same and in this case using Range is easier but I've gotten into the habit of using Cells because in the code I write I often have to increment the column and it's much easier to do that with Cells.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 1

Author Comment

by:Geekamo
ID: 38797739
@ MartinLiss,

Great, this works perfectly! Ya, I guess for me it's easier seeing the range.  I get by, by reusing pieces of code cause this is all beyond me.

Thanks!

~ Geekamo
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38797741
If you want to learn you can Google something like Excel VBA tutorial.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38797745
@ MartinLiss,

Ya, I definitely want to get more into it. I been watching Mr. Excel on YouTube and it's only been after watching some of his videos that things started clicking.  The workbook I'm working on is probably the most advanced one I've done.  I'm super excited!

~ Geekamo
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38797768
Glad I was able to help.

Marty  - MVP 2009 to 2012
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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