Loop Through An Action Within A Macro

Nov 19, 2008

I am trying to loop through an action within a macro and require some assistance.

I have the following

View 2 Replies


ADVERTISEMENT

Loop To Verify If Action Is Needed?

Jul 17, 2012

Anyway, I am basically done with a program which looks for one condition and displays an email to send as a reminder.

What I need is to have an if/then statement which checks a column in the spreadsheet and if the cell in the column states "closed" then an email is NOT displayed/sent. It seems whenever I run the macro, an email appears even if the cell for that row reads "closed."

My if/then statement in question lies following my note " 'here lies my current dilemma to solve"

Code:
Sub ThreeDayEmailTest()
'
' ThreeDayEmailTest Macro

[Code]....

View 6 Replies View Related

Loop Copy Columns Action

Sep 6, 2009

I need to copy several columns (G:L) underneath (A:F)
I got this going using the recorder, and copy the action several times in the macro (See below).

I obviously would like to have this in a loop.

Range("G1:L35000").Select
Selection.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Columns("G:L").Select
Application.CutCopyMode = False....................

View 9 Replies View Related

Take No Action In Loop If Cell Is Blank

Feb 6, 2008

I ahve looked loads on here but I really did not understand the " find" statement (!) so have had to do a lookup the only way I know - with loops.

I have two problems:
The first being that it takes 6mins to run and return values.
The second AND MOST IMPORTANT is the following....

What i want to do with the following code is - on the first "If" - if I find an emptycell/blank, i want to exit the "if" so the first "next" statement is incremented - in other words, be able to check if the next cell along is blank/non-empty

I tried using the "exit for" but I only want to opt out of the if statement - not the actual "for..next"

I would have used a goto to get to the line I wanted but it has different meanings with this language....

Numrows2 = Worksheets("File2").Range("A65536").End(xlUp).Row 'Gets the File2 Row count

For e = 2 To 7 'Cols B-G (2-7) contain the headings
For d = 1 To 229 'There are 228 cells down to check
For f = 7 To 212 'File1 has 206 cells across cols 7-212
Worksheets("Looklist").Activate
If IsEmpty(Worksheets("File2").Cells(1, f)) Then
Exit For

View 4 Replies View Related

Action When Macro Is Aborted

May 30, 2009

I have a spreadsheet that when I am running an specific macro and userforms, this one sheet is visible, when the macro is not running, then, the sheet is hidden.

Now, that happens automatically the way that I set up my macro; however, if the macro is aborted, the sheet doesn't hide.

View 7 Replies View Related

Repeating Action In A Macro

Nov 23, 2007

I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.

However, I need to repeat the same procedure 1296 times.

Please see below the macro.

Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.

Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.

The other parts of the macro will not change.

Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?

View 9 Replies View Related

Macro To Take Action On All Sheets That Come After Specified Sheet?

May 13, 2014

I have an excel Workbook made up of sheets that are named after months ("May 2014", "Jun 2014" etc). I want to pull data from each sheet that comes after a user specified date.

View 3 Replies View Related

Macro To Disallow Delete Action?

Mar 25, 2013

So I have this worksheet full of formulas that I do not want users to accidentally delete. Now, I know I can use the standard "Protect Sheet" option to not trigger any accidental changes but I would like an additional safety check (since management users will have access to unlock the sheet) via a macro to prompt the user with a message box (with Yes / No options) which essentially asks the user if they are sure they want to delete a cell if the "Delete" button is chosen? If the answer is No, nothing will happen. But if the answer is Yes, it will delete the contents in the cell.

View 1 Replies View Related

Excel 2003 :: Macro For Save As Action

Jul 12, 2014

I have some excel files which should be saved in Excel 97-2003 Workbook format . I am trying to write vba code for that i am struggling at starting . problem is i have 6 excel files which should be saved as Excel 97-2003 Workbook format i don't know how i give 6 files path to code .

View 2 Replies View Related

Macro - Making Extensive Action Items Log For Work

Apr 11, 2012

I am trying to make an extensive action items log for work.

On Sheet 1, rows 1-3 are blank. Row 4 contains column headers.

Column A: Item #
Column B: Description
Column C: Drawing #
Column D: Conforming
Column E: Nonconforming
Columns F, G, H don't really matter

What I am wanting is to be able to put an "x" or check mark in either column D or E (conforming or nonconforming). Then I want to run a macro with a push button (I know how to make these) that when pushed scans the spreadsheet row by row until it finds the "x" or check in Column E. I want it to copy every instance of this and send it to an Action Item Page that I have temporarily called Sheet 2. (This way I can go here to see all nonconforming issues). I do not want it to remove the information from Sheet 1.

Then, I want to be able to change the "x" or check on the Action Item Page to Column D (back to conforming) and the macro (push button preferably) copy and send that information back to the original spreadsheet in the SAME row it was.

So, in short I want this to be a fluid way of checking off a punchlist of items.

To complicate things even further, if possible, I would like to have several worksheets send information to the one Action Item List rather than just the one.

View 2 Replies View Related

Trigger Macro By Any User Initiated Sorting Action

Jul 26, 2014

I'm using macros to apply some complex sorting criteria. It is also applying some cell formatting specific to the sort criteria being applied.

At the start of each macro, I'm resetting all cell formatting to remove any formerly applied formatting that is not correct for the new sort criteria. Basic stuff.... works fine.

Now I need to ensure all formatting created by any macro-generated sort gets removed if a user applies a sort using any sort function from the Ribbon. (Reset all formatting prior to any 'manually applied' sort).

I can create the macro to reset the formatting, just not sure how to trigger it upon user action.

Is it possible to trigger a short macro to reset cell formatting when a user applies a sort using the Data>Sort, Data>Sort Ascending, or Data>Sort Descending commands from the Ribbon?

View 9 Replies View Related

How To Make A Macro Take Action When Data Meets Certain Criteria

Feb 6, 2010

I am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.

If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)

Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?

View 9 Replies View Related

VBA Macro To Select Cells With Certain Value And Perform Action On Adjacent Cell

Mar 10, 2014

Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.

Attached File : LoopingVBA.xlsx‎

View 3 Replies View Related

Call Or Go Back To Macro After User Action Such As Clicking In A Cell

Sep 27, 2012

I am about to write a small macro to do a iteration calculation. I would like though for the user to select me a cell so that the macro uses the value in that cell to start calculations. Now, what I think would be the problem is how do I code a "time" or notice an action such as clicking in a cell to continue with the macro.

Would it be by inserting a pause in the code of a certain time so that gives the user time to click and wait for the code to start? If so, how would much time is enough? Is there another thing that could restart the code such as "noticing a click in a cell" command in VBA?

View 1 Replies View Related

Macro To Change Range / Action Depending On Worksheet (range To Autofill)

Sep 17, 2013

Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data

On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14

I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:

Here is my macro:

Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet

[Code]....

View 8 Replies View Related

How To Set Password For An Action

Apr 27, 2009

How to set a password before executing any action.

For example, I have "cancel" button in my userform. If i click "cancel" it unloads the form. Instead of that i need a password prompt. so when i click the "cancel" button ,it should ask for a password. only if i provide the correct password then only the form should be unloaded.

View 6 Replies View Related

Put A VBA Class Into Action?

May 28, 2014

JSON import to Excel - Stack Overflow

I understand most of what this post is saying, but I don't understand how to put it together in an excel module or class module.

View 5 Replies View Related

Undo Action In VBA

Jul 18, 2006

I have a database in excel which I want only selected users to have edit rights & others should have readonly rights. I have written the following code where a pop up would appear in selected cells warning user for editing the said cell. What I want is when a user clicks yes he should be able to edit it & when he clicks no the cell should get protected.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rTriggerCell As Range
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
Set rTriggerCell = Target
Application.EnableEvents = True
If MsgBox("Edit Cell?", vbYesNo)
End If
On Error Goto 0
Exit Sub
End If

View 6 Replies View Related

Loop Macro ...

Apr 15, 2009

I found this script which is very similar to what I want to do but I am not sure how to finish modifying it.

This script compares every cell in Column B to Z on Sheet1 with Cell A1 on Sheet2, if it matches it will copy the active cell in column A and past the results to column A no Sheet3.

View 10 Replies View Related

Loop If Then Macro

May 12, 2012

I am the consolidator for a specfic Excel file to be converted into CSV. In order for this file to be uploaded into our database properly it needs to be in a specific format and certain values must be entered if other cells have been filled in. At this point I have locked the formating so users cannot change it and I have created a Macro for blank cell rules.

The Macro is to verify that cells are not blank if the value in A is not blank. At this point it works as it should, for row 4. However, I would like to Loop this Macro throughout the entire worksheet.

The Macro I have created is (minus several columns for simpler reading purposes):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("JE FILE").Range("B4").Value = "" And Worksheets("JE FILE").Range("A4").Value "" Then
MsgBox "You must fill in Customer."
Cancel = True

[Code] .......

How do I LOOP the Macro?

View 3 Replies View Related

How To Loop Macro's

Feb 28, 2008

Currently, I have two spreadsheets.

The first (SS1) is where my raw data is populated.

The second (SS2) is my template.

In the first spreadsheet, I have a macro (button) that opens up SS2.
Then in SS1, I also have another button that I click and it 'transfers' the first line of data to SS2.

In SS2, I have a save Macro, which basically saves as per the name in Cell A1.

What I would like to do is to loop the process, i.e. when I click the 'Transfer' button in SS1, it will open up the 'template' spreadsheet (SS2), copy the information across, save & close and repeat this for the remaining data in SS1.

Is there a simple coding to use that can loop this process for me?

I would attach the two spreadsheets for you to view but I am unsure how to upload on here.

View 9 Replies View Related

Macro Loop

Dec 10, 2008

I would like the macro to loop through rows and highlight the cell in column G if the value is at least 2x greater than the value in the cell in column D.

Right now I have a very long macro... but I'd like to change it so goes through all the rows I assign it to instead of writing many lines of code (lots of if then statements):

Sub Macro4()
Range("G28").Select
If Range("G28") > 2 * Range("D28") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If

View 9 Replies View Related

Loop In Macro

Apr 30, 2009

if i am in an active cell which is empty and want to stop the macro at this point, what code should i type in visual basic to achieve this

View 9 Replies View Related

Macro For Loop

Aug 4, 2009

I'm trying to get this macro to loop and have no idea what I need to do do to get the macro to keep repeating until it gets to the last item in a list.

I have a list of addresses (some are 3 rows long, some 4 and some 5). And I want to Copy each one then paste special in the empty cell between it & the next address entry.

Here's the code for my simple macro that does one copy & paste ... I have to do each one manually & would like to modify the macro to go all the way to the end of my list.

I read that there are 4 types of loops (While Loop, For Loop, Do While, ??) ... don't know how to choose which one or how to use/format with my exisiting macro.

Sub Transpose_j()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub..........................

View 9 Replies View Related

Loop Macro

Jun 17, 2006

I have record a macro to sort the data on one .csv file .

I have about 1000 of this similiar .csv files, I would like to have a macro loop to repeat the sorting and arrangement .

Workbooks.Open Filename:= _
"G:Data20060616.csv"
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

View 3 Replies View Related

Mulitple Action When Condition Met

Oct 21, 2008

How do you set the code so that, if a condition is met, then several cells are filled with values?

So far, my code is

HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If

View 11 Replies View Related

Cancel A Message Box Pop Up Action

Aug 13, 2009

If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.

Can this code be modified so that the message box does the following...

1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.

2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.

I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.

3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this).

View 4 Replies View Related

Comment Out Using VBA - How To Create This Action

Feb 1, 2012

I have created vba which I want to comment out. I totally stumped how to create this action on vba. My current vba code which I want to comment out using vba

Code:
Option Explicit
Sub YearlyInput()
End Sub
'Speeding Up VBA Code
With Application

[Code] .......

View 8 Replies View Related

Cursor Not Stopping While TAB Action?

Aug 24, 2012

I have a for which consists of

2textbox
1 listbox
1 textbox.

While entering the data, the cursor does not stop in the list box and goes directly goes to the next textbox. Autotab is set to true.

View 4 Replies View Related

Personal.xls Is Missing In Action

Feb 25, 2008

Last friday my personal.xls file went missing. I cannot find it anywhere which means I am missing about 10 key macros. While it is not the upset of the century, my problem is that now when I go into Record Macro and try to "Store New Macro in:" -> "Personal Macro Workbook" and then click OK, Excel brings up a msg box that says:

Personal Macro Workbook in the startup folder must stay open for recording.

Click OK and Excel says:

Unable to Record

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved