Free Microsoft Excel 2013 Quick Reference

"Do Nothing" - Macro

Hi,

I've got a problem with an updating list. What im trying to do is to make it perform a calculation depanding on which date it is. If it is todays date, i want it to perform the calculation, but if it is not i simply just want it to do nothing. The reason for this is that i need to save the calculations for the dates passed, so they are not overwritten. The list looks kinda like this:

"YES" "NO"
01-May 13 5 <-- need tosave this history
02-May calc. calc. <-- today
03-May 0 0 <-- going to be calculated next day

The formula would want to use (which i cant since formulas cant be used here) would look something like this:

=IF(TODAY()=A1;COUNTIF(Sheet1$A$2:$A$430;"YES");DO_NOTHING)

Help anyone?


Hello,

I'm not sure how to find the last row WITH DATA for AutoFill purposes after executing a command such as =if(*Cell satisfies some criterion*,"delete",""), and then sorting the column.

E.g. a list of names, run an If(or(a, b, c), "keep","") statement and autofill to end, then sort. Say there's 73 names with "keep" - how do I find the 74th row and delete it?

Even after a copy/PasteValue, a command such as myLastRow = Range("A65000").End(xlUp).Row is returning the last "" row from my initial If statement, rather than the last row with data.

Maybe I need some do-nothing value/function, as in =If(TRUE,"keep",*DO NOTHING*)??

Please help! I need this for just about every macro I use... so aggravating. Thank you

Hi,

One of the members kindly gave me this macro to overcome a problem a couple of weeks ago.

It basically runs down through two reports and if it finds a code on one that isn't on the other it creates a new worksheet "New Codes Not On Priam Report" and pops the codes in there.

I would like to adjust if to do nothing if there are no new codes at the moment it stops here

All help very much appreciated.

Option Explicit
Sub test()
Dim x As String, y, b, i As Long, j As Long
Application.ScreenUpdating = False
With Sheets("Priam Report"): x = "$" & Join(Application.Transpose(.Range(.[b8], .Cells(Rows.Count,
"b").End(xlUp))), "$") & "$": End With
With Sheets("Download"): y = .Range([b8], Cells(Rows.Count, "b").End(xlUp)): End With
ReDim b(1 To UBound(y), 1 To 1)
For i = 1 To UBound(y)
    If y(i, 1) <> "" Then
        If InStr(1, x, "$" & y(i, 1) & "$") = 0 Then
            j = j + 1: b(j, 1) = y(i, 1)
        End If
    End If
Next
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "New Codes Not On Priam Report": [a1].Resize(j, 1) = b:
[a:a].EntireColumn.AutoFit
Application.ScreenUpdating = True:
End Sub


I am trying to create an Expense Statement worksheet for my users. When the user opens a Statement for the first time, the date field at the top is blank. The first column is where they enter a date. I want the spreadsheet to use the date from the first entry (wherever that entry may be in the date column) and display it in the date field at the top. If the date field at the top has already been filled, then it is not to alter the contents of the field. It is supposed to leave the cell alone and do nothing.

Does anyone have any suggestions for how I can construct my IF statement to handle this problem. In effect, I want to create an IF statement which says

If count(A1:A20)=1,
then update the cell
else leave the cell alone

Thank you for your ideas

Brian

Hi all,

Searched with no luck for an answer on this.... When I use "Select Case", how do I get my code to do nothing in certain circumstances?

Example (and the maximum value of i could be any integer, as defined elsewhere):

Code:
    Select Case i
        Case 1
            'do something
        Case 2 - 5
            'do something else
        Case 6 - 8
            'do nothing at all

Hi I have an number of if statemnest within and average formula. If the if statements are false i want nothing to be returned. Currently I know that if I leave the false section of the formula blank as default it leaves a '0' which is messing up the average result. Is there any way to tell the if statemnet to do nothing if the criteria is not met. The formula looks like this.

=AVERAGE(IF(AN11="t+1",AN10,WHAT DO I PUT HERE),...........other similar if statements

Please anybody can tellme why when I point on any cell of any excel
file, and try to do one click with my right button on my mouse, dont
showme the options ?
because my right button dont do nothing, and if I want to format any
specific cell, I need to go to "Format" and then the options on the
menu bar...

In the past this option its working very well, but today its gone !!

can any body tellme what its happening ? ( my mouse its working good in
other programs or on windows )

thanks

--
rapidito78840
------------------------------------------------------------------------
rapidito78840's Profile: http://www.excelforum.com/member.php...o&userid=26326
View this thread: http://www.excelforum.com/showthread...hreadid=400317

Open a file do a macro ( made) and open next succesive file

File name yyyy-mm-ddxxxx.csv

Open a file do a macro ( made) and open next succesive file

File name yyyy-mm-ddxxxx.csv <--- the date is the creation date.
can I have excel run the macro to auto matically open first file in dir run
its parse and save ( this is done) THEN open the next file and continue?

Please anybody can tellme why when I point on any cell of any excel file, and try to do one click with my right button on my mouse, dont showme the options ?
because my right button dont do nothing, and if I want to format any specific cell, I need to go to "Format" and then the options on the menu bar...

In the past this option its working very well, but today its gone !!

can any body tellme what its happening ? ( my mouse its working good in other programs or on windows )

thanks

Dim NRepl1 As String
Dim mFind As Range

With Me
NRepl1 = .Listbox1.Column(0)
End With
**If NRepl1.Value = txtName.Value Then
**'Do Nothing else
If Len(NRepl1) Then
Set mFind = Columns(1).Find(What:=NRepl1, _
                          LookIn:=xlValues, _
                          LookAt:=xlWhole, _
                          MatchCase:=False, _
                          SearchFormat:=False)
                          
Do While Not mFind Is Nothing
mFind = txtName.Value
Set mFind = Columns(1).FindNext
Loop
End If

Can anybody help me with a line that will execute the acction marked with and asteriks in the code?

Thanks!

I want to cancel out the then part of an if/then but not change anything else.
I want it to do "nothing" instead of what it is doing.
I tried commenting out the then part and I got a run time error so I just
want it to exit the then portion and keep running.

if range.value = placeholder
then exit if

Can you use the command "exit if"
I'm trying to exit the If statement and keep the procedure running but void
out the if/then statement without changing any placeholder references.
thanks,

I want to cancel out the then part of an if/then but not change anything else.
I want it to do "nothing" instead of what it is doing.
I tried commenting out the then part and I got a run time error so I just
want it to exit the then portion and keep running.

if xxxxxx
then "do nothing"

thanks,

I need help writing code for the following process:

I have a 'master' workbook consisting of 3 sheets; data, table and pie
chart. In addition I have multiple Excel workbooks, each with 'data'
for a different geography. These 'data' files have the identical
range; A1:Q18. The scenario is 50+ 'data' workbooks open, along with
the 'master' workbook containing the 'report' and 'pie chart'. The
'report' tab is linked to the 'data' sheet, while the 'pie chart' is
linked to the report. I'm trying to write a 'Do Until' macro that
begins with the first 'data' file, copies an exact range from it,
pastes the range onto the 'data' sheet in the 'master' workbook at cell
A1, renames the workbook based on a name in a specific cell of the new
'data'. Then 'saves and closes' the new workbook, and then repeats the
process with the next 'data' workbook.

My problem is once I rename the original 'master' workbook it's closed,
so my Do Until statement stops.

Does anyone have any ideas on how I can write code to make this work.

Thank you,

Tony

In my spreadsheet it contains a daily column and off to the right the monthly columns which are then totaled. The monthly columns contain a column for each day of the month. The user enters the daily amounts in column A then was copying and pasting those amounts to the appropriate column in the monthly section for that day of the month. I want to create a formula that populates the monthly section based on the day of the month.

If in cell A1 they enter the date of the month.
If I use this formula off to the right in say column 15 for the 15th of the month and I want the amount from field A5 in that field if the date is the 15th.(if A1=15, A5) it zeros out all the days that are not the 15th. I want the amounts of the 1st to the 14th not to change when it is the 15th.

How do I make the else part of the statement to do nothing?

I have created a formulae that asks if A1 = B1 then return the number 3 and
if not do nothing (for example =IF(A1=B1,3,"") and it works fine as long as
there are numbers there. My problem is that i want to set up a table and
this equasion has been draged down the page but when the cells are blank it
returns a 3 (because both cells are the same) and i only want it to show when
a number has been inserted to the relavant cells.
Please help it's driving me nuts.

To avoid compromise of personal information, I would like get help to do a macro to remove the first five of social security number and leave only the last four. I appreciate any help.

How to do a macro for Payment Voucher, Cash Book, and Bank Checks/Cheques?

I shall be much obliged if you guys could kindly help me using VBA codes or Macro to run the following.

1. I have worksheet named Payment Voucher in Book1.
1.1 Date in Cell G1
1.2 Name in Cell B4
1.3 Invoice amount in Cell B7
1.4 Amount in Number [1,450,000.38] in Cell F7

2. I hope that you guys could help me to do the following:-

2.1 When I key DATE in Cell G1 at Payment Voucher, DATE automatically appear in the following 2 worksheets.
2.1A) Cell A2 of worksheet Cash Book (Book2)
2.1B) Cell G1 of worksheet Bank Check (Book3)

2.2 When I key NAME in Cell B4 at Payment Voucher, NAME automatically appear in the following 2 worksheets.
2.2A) Cell C2 of worksheet Cash Book (Book2)
2.2B) Cell B4 of worksheet Bank Check (Book3)

2.3 When I key AMOUNT in Cell F7 at Payment Voucher, NUMBER automatically appear in the following 2 worksheets.
2.3.A) Cell E2 of worksheet Cash Book (Book2)
2.3.B) Cell F6 of worksheet Bank Check (Book3)
2.3.C) Cell B6 of worksheet Bank Check, Number in TEXT must appear like this: One Million Four Hundred Fifty Thousand And Cents Thirty Eight Only

Any suggestion and advice is highly welcomed and appreciated.

Thousand thanks in advance.

Susan

Hello,

I am trying to add images to the macros available on the quick access toolbar -- attached is a workbook in which I have added 4 do-nothing macros to the quick-access toolbar.

I'm not sure if they will show up for everyone the same way...so I included an image of that portion of the toolbar.

QUestion: How do I add a helpful image to the button to replace the default button image? I would like the user to intuitively know which button to mouse-over to launch the different macros.

Thank you

Hi,

I had previously created and used a custom menu using just VBA code but this became difficult to manage and add to as the custom requirement grew. I therefore searched for an alternative method and found what I think is John Walkenbach's menu maker example which utilises a standard code with the menu items detailed in a worksheet.

I downloaded the example and tested it and all was well and therefore I copied and pasted the code and the worksheet into the desired workbook. When running the code in my new workbook i recieve a run-time error '9' on the following line (shown in bold italics);


	VB:
	
 MenuLevel 
Case 1 ' A Menu
     '              Add the top-level menu to the Worksheet CommandBar
    [B][I][COLOR=black]Set MenuObject = Application.CommandBars(1). _[/COLOR][/I][/B] 
    [COLOR=black][B][I]                 Controls.Add(Type:=msoControlPopup, _[/I][/B][/COLOR] 
    [COLOR=black][B][I]                 Before:=PositionOrMacro, _[/I][/B][/COLOR] 
    [COLOR=black][B][I]                 Temporary:=True)[/I][/B][/COLOR] 
    MenuObject.Caption = Caption 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The complication arises after having attempted to run this code in my new book the original code no longer works either in the original downloaded file? I then tried using another pc and again, the original code works until i try and run it in my new book, and then neither work?

Can anyone help?

The full downloaded code is given below;


	VB:
	
 
 
Sub CreateMenu() 
     '   This sub should be executed when the workbook is opened.
     '   NOTE: There is no error handling in this subroutine
    Dim MenuSheet As Worksheet 
    Dim MenuObject As CommandBarPopup 
    Dim MenuItem As Object 
    Dim SubMenuItem As CommandBarButton 
    Dim Row As Integer 
    Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId 
     ''''''''''''''''''''''''''''''''''''''''''''''''''''
     '   Location for menu data
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet") 
     ''''''''''''''''''''''''''''''''''''''''''''''''''''
     '   Make sure the menus aren't duplicated
    Call DeleteMenu 
     
     '   Initialize the row counter
    Row = 2 
     '   Add the menus, menu items and submenu items using
     '   data stored on MenuSheet
     
    Do Until IsEmpty(MenuSheet.Cells(Row, 1)) 
        With MenuSheet 
            MenuLevel = .Cells(Row, 1) 
            Caption = .Cells(Row, 2) 
            PositionOrMacro = .Cells(Row, 3) 
            Divider = .Cells(Row, 4) 
            FaceId = .Cells(Row, 5) 
            NextLevel = .Cells(Row + 1, 1) 
        End With 
         
        Select Case MenuLevel 
        Case 1 ' A Menu
             '              Add the top-level menu to the Worksheet CommandBar
            Set MenuObject = Application.CommandBars(1). _ 
            Controls.Add(Type:=msoControlPopup, _ 
            Before:=PositionOrMacro, _ 
            Temporary:=True) 
            MenuObject.Caption = Caption 
             
        Case 2 ' A Menu Item
            If NextLevel = 3 Then 
                Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup) 
            Else 
                Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton) 
                MenuItem.OnAction = PositionOrMacro 
            End If 
            MenuItem.Caption = Caption 
            If FaceId  "" Then MenuItem.FaceId = FaceId 
            If Divider Then MenuItem.BeginGroup = True 
             
        Case 3 ' A SubMenu Item
            Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton) 
            SubMenuItem.Caption = Caption 
            SubMenuItem.OnAction = PositionOrMacro 
            If FaceId  "" Then SubMenuItem.FaceId = FaceId 
            If Divider Then SubMenuItem.BeginGroup = True 
        End Select 
        Row = Row + 1 
    Loop 
End Sub 
 
Sub DeleteMenu() 
     '   This sub should be executed when the workbook is closed
     '   Deletes the Menus
    Dim MenuSheet As Worksheet 
    Dim Row As Integer 
    Dim Caption As String 
     
    On Error Resume Next 
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet") 
    Row = 2 
    Do Until IsEmpty(MenuSheet.Cells(Row, 1)) 
        If MenuSheet.Cells(Row, 1) = 1 Then 
            Caption = MenuSheet.Cells(Row, 2) 
            Application.CommandBars(1).Controls(Caption).Delete 
        End If 
        Row = Row + 1 
    Loop 
    On Error Goto 0 
End Sub 
 
Sub DummyMacro() 
    MsgBox "This is a do-nothing macro." 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Apologies for the lengthy post.

Thanks
Nick

For example, if cell A1=1, I want to run Macro1 once. If cell A1=2, I want
to run Macro2 once. If cell A1=3, do nothing.

I am familiar with Excel If/Then statements, so to my thinking, it would
look something like:

IF(A1=1,Run Macro1,IF(A1=2,Run Macro2,""))

I just don't know what the command is to "Run" the macro.

(3rd repost of this question - no responses)

Column B contains a bunch of information for Monday, March 23rd. Column C
contains a bunch of information for Tuesday, March 24th and so on. The data
is set up so it’s easy to print out on a sheet, and every week the
information is overwritten with the new week’s information. Column B becomes
Monday, March 30th, column C becomes Tuesday, March 31st and so on. Every
week the dates are updated and the columns re-written.

I want to preserve the information so long-term trends etc can be analyzed.
So I’m thinking that I’ll pull the information into another sheet for each
day, and wow wouldn’t it be great if I could do this automatically. I’ll set
up another sheet where each row represents a date: row 1 will be March 23rd,
row 2 will be March 24th and so on. Then the information from Monday, March
23 (column B) can be placed into the first row, the information from Tuesday,
March 24(column C) can be placed into the second row, and so on. It would be
a real-time update so as the information for the current day changes then the
preserved information also changes, and the beauty is that once that day is
over and it’s the next day, then that preserved information from the day
before is no longer touched.

How do I do it? LOL.

You’d think the formula “=B1” would do what I need, but B1 will eventually
represent the next week. I don’t want to overwrite my preserved information
from the week before so I need a condition – an IF function based on date.
But an IF function requires a false action : IF true then copy, IF false
then…two quotes? A zero? Hmmm. No matter what I place into the function, the
false condition blanks my data. I cant find a way to leave the data alone.
I cannot preserve it.

The problem with using a formula to grab data from a fixed location such as
the numbers in a cell black is that the source numbers will eventually
change, and since the formula to grab that information will always remain
active, it too will eventually overwrite the original data I wanted to
preserve, which is what I don't want, or it's going to return a "" or zero
value because I tried to make it conditional on date March 23 only, which is
also what I don't want.

I need a "do nothing" so March 23 is preserved once it's written into the
row that preserves information for March 23 and it’s no longer March 23rd.
I'm thinking I need a macro, VBA, something.

I need a macro or VBA code to say :

IF today is March 23, 2009 then write to here, else leave this location
alone (don't write "" double quotes, don't write a zero, dont erase the
information you wrote earlier, just leave this location alone and move on,
whatever number that was brought to this location on March 23, 2009
just leave it alone, it must not be March 23, 2009 anymore so move on).

The next row, because it's a new day, will be similar : IF today is March
24, 2009 then write to here, else...yadda yadda yadda

And so on for the week of March 23, 2009.

Next week column B now represents March 30, 2009, and there's a new row for
preserving the information: IF today is March 30, 2009 then write to here,
else...yadda yadda yadda

Preservation Sheet:
Row 1 : March 23 – the information for March 23 goes here.
Row 2 : March 24 – the information for March 24 goes here.

Row 8 : March 30 – the information for March 30 goes here.


- Eric

For example, if cell A1=1, I want to run Macro1 once. If cell A1=2, I want
to run Macro2 once. If cell A1=3, do nothing.

I am familiar with Excel If/Then statements, so to my thinking, it would
look something like:

IF(A1=1,Run Macro1,IF(A1=2,Run Macro2,""))

I just don't know what the command is to "Run" the macro.

Hi there,

I've recently been writing an excel spreadsheet and have encountered a weird problem.
i have a workbook where buttons open userforms which add and remove worksheets.

Clicking a button opens a userform called DelSysForm which asks weather you want to delete the sheet or not.
the code for the form is


	VB:
	
 Userform_Initialize() 
    Label1.Caption = "Are you sure you wish to delete the system?" & vbNewLine & "This process is not reversible" 
    Noshts = Sheets("Info").Range("TotSysRng").Value 
    NoDel = Range("PgNo").Value 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
NoDel and Noshts are private integers

the coding for the userform button is this


	VB:
	
 
Private Sub Yes_Click() 
    Call DelSheets(NoDel, Noshts) 
    Unload DelSysForm 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the DelSheets macro coding is


	VB:
	
 DelSheets(ShtNo, TotShts) 
    Dim i As Long, StrNu As String 
    For i = 1 To TotShts 
        StrNu = CStr(i) 
        If i < ShtNo Then 
             'Do Nothing
        ElseIf i = ShtNo Then 
             'delete sheet
            Sheets("System " & StrNu).Delete 
             
             
        ElseIf i > ShtNo Then 
             'rename sheets
        End If 
         
         
    Next i 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now what happens next is really weird.
When I try to run the userform from the worksheet the code runs smoothly but just after the process is completed excel crashes and i have to restart.
I tried debugging through the whole process and found that it is all working perfectly. the sheet to be deleted is removed etc but then at the end of the process once again excel crashes.
So i tried a few other things. I made a new userform and rewrote the code but once again crashed
I then tried not deleting the worksheet i.e. made the line "Sheets("System " & StrNu).Delete" into comments and presto didn't crash.
I also tried running the userform from vb editor and presto didn't crash
I should also point out that it only crashes when ShtNo is greater than one.

i am completely baffeled. I've been trying for a few hours to figure out what is causing the error to no avail
If anyone has any suggestions please help.
I dont mind changing the code completely if that creates a solution but right now this seems the easiest and best way to do it

Cheers

and by the way where ive put rename sheets is purely because i havent written that part yet

The raw data is on sheet "Full Price List" and is arranged in four columns: item number, item description, pricing category, and list price. There are approximately 40,000 entries.

The primary sheet contains an order form where the customer selects which of the first three data categories to base the order on (item number, item description, or pricing category). Data validation in cell C13 is used to activate a macro that formats the rest of the form by setting up data validation for the selected basis, and vlookup to fill in the remaining data.

I'm currently using Worksheet_Change and a Select/Case statement to accomplish this, but when something in C13 is selected or any other action is taken anywhere on the sheet, it falls into an infinite loop and crashes excel.

Here is the offending code:


	VB:
	
Private Sub Worksheet_Change(ByVal Target As Range) 
    Select Case Range("C13").Value 
         
         'user has selected to fill in form using item numbers
    Case "Item Number" 
         
         'sets up data validation for item numbers
        Range("B18:C29").Select 
         
        With Selection.Validation 
             
            .Delete 
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
            xlBetween, Formula1:="=Items" 
            .IgnoreBlank = True 
            .InCellDropdown = True 
            .InputTitle = "" 
            .ErrorTitle = "" 
            .InputMessage = "" 
            .ErrorMessage = "" 
            .ShowInput = True 
            .ShowError = True 
             
        End With 
         
         'fills in item description from raw data using vlookup
        Range("D18:F18").Select 
        ActiveCell.FormulaR1C1 = _ 
        "=IF(ISNA(VLOOKUP(RC[-2],'Full Price List'!R2C1:R40031C2, 2, FALSE)) = TRUE, "" "", VLOOKUP(RC[-2],'Full Price
List'!R2C1:R40031C2, 2, FALSE))" 
        Range("D18:F18").Select 
        Selection.AutoFill Destination:=Range("D18:F29"), Type:=xlFillDefault 
        Range("D18:F29").Select 
         
         'fills in list price from raw data using vlookup
        Range("G18").Select 
        ActiveCell.FormulaR1C1 = _ 
        "=IF(ISNA(VLOOKUP(RC[-5], 'Full Price List'!R1C1:R40031C3, 3, FALSE)) = TRUE, "" "", VLOOKUP(RC[-5], 'Full Price
List'!R1C1:R40031C3, 3, FALSE))" 
        Range("G18").Select 
        Selection.AutoFill Destination:=Range("G18:G29"), Type:=xlFillDefault 
        Range("G18:G29").Select 
         
         'user has selected to fill in form using item description
    Case "Item Description" 
         'to be filled in
         'user has selected to fill in form using pricing category
    Case "Pricing Category" 
         'to be filled in
    Case Else 
         'do nothing
         
    End Sub '========================================

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope I adhered to the rules, thanks in advance.