Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-08-2017, 09:20 PM
AJacLo AJacLo is offline Help with Code Windows 10 Help with Code Office 2016
Novice
Help with Code
 
Join Date: Feb 2017
Posts: 1
AJacLo is on a distinguished road
Default Help with Code

After I run this macro again, I get the same values. Can someone tell me how to reset the specified range "Table," so that I can add another range and calculate new values?




Code:
Sub MacroTry1()
'
' MacroTry1 Macro
Dim Specifiedrange As Range
Set Specifiedrange = Application.InputBox("Specify the range:", Type:=8)
Specifiedrange.Name = "Table"
ActiveCell.FormulaR1C1 = "=COUNTIF(Table,"">0"")"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNT(Table)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("D3").Select
End Sub

Last edited by macropod; 02-10-2017 at 11:17 PM. Reason: Added code tags
Reply With Quote
  #2  
Old 02-19-2017, 03:46 PM
BobBridges's Avatar
BobBridges BobBridges is offline Help with Code Windows 7 64bit Help with Code Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I haven't been back here in a while, AJacLo; I don't know what you mean by "the same values". The same as what? I guess you're referring to a previous thread, but you don't cite it here.

As I read your code, it asks the user to specify a range. It puts in some random cell—the cell that happened to be active when you started the program—a formula counting the number of non-zero values in the range. Then in E2 it puts the number of values (zero or non-zero) in the same range. In F2 it puts the difference between the E2 and D2. If the user started the program while D2 was active, then that third figure shows the number of zero values in the specified range.

Of course I may be misreading it. But to help I still gotta know "the same values as what"?
Reply With Quote
  #3  
Old 02-19-2017, 03:57 PM
Logit Logit is offline Help with Code Windows 10 Help with Code Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Option Explicit

Sub MacroTry1()
'
' MacroTry1 Macro
Dim Specifiedrange As Range
Set Specifiedrange = Application.InputBox("Specify the range:", Type:=8)
Specifiedrange.Name = "Table"
ActiveCell.FormulaR1C1 = "=COUNTIF(Table,"">0"")"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNT(Table)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("D3").Select

Set Specifiedrange = Nothing '<--- ????

End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Code VBA Code to search for field codes with certain text before the Field code and to change style welcometocandyland Word VBA 4 02-08-2017 06:53 PM
Help with Code QR-code qrcode Word 3 05-26-2015 06:38 AM
Help with Code code to run once only gerrymac Word VBA 6 07-26-2014 12:19 AM
Help with Code Where does my code go? rbaldwin Word VBA 3 03-14-2012 02:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft