Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Macro Code Needs Revision

Posted on 2013-01-19
7
Medium Priority
?
222 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
  • 4
  • 3
7 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 49

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 49

Expert Comment

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

Marty  - MVP 2009 to 2012
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

886 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