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
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.
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.
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
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..........................
I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.
Here's the macro:
[Code] ......
This continues over to column AO so far then I drop down a few rows and do it again:
[Code] .....
I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?
I want to loop through all files in a directory and run a macro named "Main" in each file. The file that contain the loop-macro will stay in the same directory as the files I loop through.
I have a number of workbooks which contain 62 tabs each. I need a macro that copies specific information from each tab and pastes it to a summary table which will be uplaoded to an access database. I created a macro that works when I manually click on each tab and run the macro. I then added a loop to try and automate. when I run the macro, it loops through all of the tabs, but it only copies and pastes from the first tab, resulting in 62 line items fron the 1st tab. I need to know how to set the active tab to which ever tab the loop is on.
I have a row of data. The first few columns are given data, and then the next few columns are equations that use the first few columns of data.
The next row down adds the result of a few of the equations in the row above it to the given data, and the equations are calculated again.
It looks a bit like this:
1231437-111213-9
in the first row, 1 and 2 are just given. 3 is just 1+2 (a1+b1), and in the last column, 1 is just 2-1 (b1-a1). In the second row, A2 is A1+C1, and B2 is just B1+D1, C2 and D2 are the same calcs as C1 and D2.
This row is then filled down.
What I would like to do, is just calculate what the first two columns of data are by just entering the number of cycles id like to make, instead of filling down the row.
For example, I would like to just enter N=3 in a cell, and it returns 11 and 2. Or N = 1000 and it would give me whatever the first two values are if i had filled the row down 1000 rows.
I have a table sorted on column A. I need a macro to go row by row on that list and stop when the NAME (column A) is changed, in this example 3 first rows "aa". then I need to define the range of these 3 rows (A2:D4) in the table and make some subroutine. After finishing with this range I need the macro to continue and find the next rows with the same name, define the Range and go to the subroutine (in my example A5:D5)
The next range will be A6:D7 and so on until last range A27:D27. How can it be done?
I tried some CASE, FOR NEXT, DO UNTIL loops to do this but get stuck.
DATA *ABCD1NAMEDATEALERTTEST 2aa10HB3aa20INR4aa41WBC5ww50ELE6zz30DIG7zz61HYD8dd21CRE9dd41PLT10dd60HB11dd71INR1 2rr10WBC13tt20ELE14tt41DIG15yy40HYD16uu51CRE17ii20PLT18ii31HB19ii51INR20ii70WBC21ii80 ELE22oo20DIG23ll11HYD24ll20CRE25ll30PLT26mm21HB27nn30INR
I have worked out how to get my macro behaving the way I want however now I would like to automate the whole process. Basically I would like the macro to work its way down a column (in this example column F) until it has completed and there is no more data in the row before the last.
Here is a code snippet of my macro
Code: Keyboard Shortcut: Ctrl+a' If ActiveCell = 2013 Then ActiveCell.Offset(, 2).Range("A1:E1").Select Selection.Cut ActiveWindow.SmallScroll ToRight:=24 ActiveCell.Offset(0, 24).Range("A1").Select ActiveSheet.Paste End If
End Sub It should simply delete picture 1, picture 2, picture 3, picture 4, etc. The problem lies within the "Picture " & count part.
This is obviously the name of the picture, i.e. picture 1, picture 2, etc.
That's what I tried to accomplish with the for-loop, but I'm not sure how to increment the number of the picture by means of using a for-loop and having that be the new name for the picture.
I have a macro which checks a column for a name, and any instances of those names it finds it copies and pastes into a new spreadsheet which it saves to a path of my choosing and closes it, then moves onto the next one. However, my macro is stopping halfway through my sheet and one of the lines of code is being highlighted as an error by the debugger - even though the same line has just worked for the last 60 instances or so.
is there a limit to how many times I can loop a macro?
I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?
I've taken over a spreadsheet that has a macro in it. However it doesn't seen to function properly. The big issue with the Macro is that it is only running for the first 80 lines of data. I can't have a restricted range as the number of lines could increase/decrease each month depending on activity. I've attached the code.
Sub Macro1() Dim strTemp As String Dim intSpace As Integer Dim intZero As Integer Dim i As Integer Dim strCat As String Dim strZero As String Dim intLoopCounter As Integer Dim rw, this intLoopCounter = 1 For Each rw In Worksheets(1).Cells(1, 1). CurrentRegion.Rows this = rw.Cells(1, 1).Value ...................
Need some alternative code that would speed the execution of this macro. My weak attempt runs noticeably SLOW.
Dim cell As Range For Each cell In Range("TPD") If IsNumeric(cell) And Not IsEmpty(cell) Then If cell.Value > 0 Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If End If On Error Goto 0 Next End Sub
I am running a macro to loop through a list of codes which takes the unique code, goes to a different spreadsheet, finds related data based on that code and if it meets certain criteria, and retursn the same code if it works. Pretty much a filtering process, but I have it returning the code because I do not want to do the calculations in the Macro itself.
Here is the code:
VB: On Error Goto Label1 Do Windows("Byron_MonthlyPDPWellList.xlsx").Activate Range("A" & I).Select
[Code] .....
"I" begins at 2 and counts up until it reaches Count, which is the number of total rows in the column.
However, when I step through the code, it continues to loop through the code where "I > Count".
I have tried "Until I > Count", "Until I = Count", "While I <= Count". And none of them seem to work. It gets stuck in an endless loop cycle.
I have a financial model (attached) which calculates the final cash flows (in a calculation sheet named 9.Operations, rows 428 to 437) for a particular plot. However,the model has a list of 17 plots in the input sheet (3.Input-Plot Details). The macro must change the plot number in the 9.Operations sheet(cell G11), re-calculate the final cash flows and paste all the final cash flows for 1 to 17 plots in a single separate sheet consecutively.
The way the code works right now is that I have it run as a few loops. it will first loop through the first worksheet in the array and delete what I need and then when that loop finishes it will run two macros that I have added in, then it will loop to the next worksheet in the array. right now it is only 2 sheets but eventually it will be many more. just working out the code for now. What I would like to do is in the second loop. my second macro in the code formats each page and, what I would like to do is add a date column to my code. I need to start at 1/05/2014 and then for the next iteration in the loop add 7 days.
i have the following macro that loops through 100 tickers in sheet 1, gets the trades for each, pushes each ticker's trades to sheet 2 to create a list of all trades. The problem is that I just get a #N/A requesting data for each ticker. This seems to be a wider problem with fetching external data from Bloomberg. However, I cannot seem to get a fix for it.
Sub Macro() On Error Resume Next Sheet2.Range("B3:E65536").ClearContents
I tried to create a macro that compares two columns using a match function. The macro adds the macro function to the first cell and I double clicked the bottom right of the cell to have the formula auto fill down the column.
I noticed that the macro records the exact number of rows when you perform the auto fill or fill series. Now if I add another couple rows to the sheet, the step to auto fill all the cells in the column it stops before the bottom of the column.
I have been searching for a way to make sure the formula is placed in all the cells. I have been learning about loops but am having a problem building it.
I have a complex sheet where rows are continuosly overwritten and new data needs to be formatted each time. My macro works great aside from the fact the conditional format formula expression adds double quotes rendering the conditional format useless. After running the macro I can go into the conditional format field, remove the quotes, and the formatting formula works as expected. So...I first did a "record macro" to grab the code for conditional formatting:
