The following is the macro I have created, but I can't get it to loop. I need to insert a row below Labour-Overtime about 100 times in the document, not just once. Until the bottom. Also, active cell 1013 needs to be the same as what is above it. The series repeats and goes up by 1 every time. Not sure how to get that to work.
View 5 Replies
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code:
Option Explicit
Sub get_data_from_2()
Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range
[Code] .......
the bolded part gives me an error.
I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.
The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?
Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select
[Code] .....
I recorded the following macro to move all entries in a sheet to a single column:
[Code] .....
Before the macro runs, my data is 11 rows long, after running the macro my data is 17 rows long.
What would be the syntax to add a loop for the macro to repeat the procedure every 11 rows until reaching the end of the data?
I have a data set like this
Date ID
3/1/13 ID1
4/1/13 ID2
5/1/13 ID3
6/1/13 ID1
7/1/13 ID1
Now i want a VBA Procedure/function that gives the row no. of the row in which ID1 is appearing last. I can do this by a worksheet formula in worksheet or by looping in VBA but I need it in VBA without looping. The reason behind avoiding loop is data can be extended up to 50000 rows,so looping will take too much time to complete.
I'm trying to come up with an array formula to count the consecutive declines or increases from the last value in a column, going from bottom to top. The real world application is a list of daily stock prices down a column, and counting from the most recent day, how many consecutive days the price declined or increased.
I got some hints in the following post but can't get it evaluate the resulting logical array in reverse order (which is needed since column addresses are evaluated top to bottom, not bottom to top).
Count consecutive increase in values
Example for consecutive increases leading to last value
49
50
51
52
53
49
55
56
I need a formula to return 2, the number of increases from 49 to 56.
I have a cell (c9) that increases on an integer basis using a rounddown function that in turn is looking at a calculation in a different cell. Is it possible to create a pop as the integer value in c9 increases by 1?
View 3 Replies View RelatedIn sheet 1 of my workbook I have a spreadsheet with lots of different data, most of this data is pulled from our own internal process books.
The problem I need help with is,
Cell "A5" contains a storage tank level.
Cell "A6" contains the date the storage tank was last filled, (this info is required for auditing purposes).
If possible I would like to automate the date entry.
So when the level increases by say 2.0 I would like the new date in cell "A6". I don't want that date to change if the level stops the same or decreases. So the date will not change again until the storage tank has been emptied and refilled.
I want to make a dynamic chart that grows as the data increases
The data is from C23:O23 (12 Columns)
The Months are from C22:O22 (12 Months)
I am only on month 4 (April) but this will eventually but 12 months
Looking at various sites you have to use a dynamic named range and in the Refers box drop in an OFFSET formula.
My problem is I cannot figure out an OFFSET formula for columns instead of rows.
I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:
Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN
I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).
Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.
how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?
I am using Excel 2003 on Windows XP, and I have created a form using several TextBox controls on Worksheets (not userforms). When I click on a textbox that has some text in it, the size of the text grows. Clicking away from it, and then clicking on it again causes the text to grow further. Repeating these actions ultimately causes the text to be so large that the contents are illegible. There is no VBA code manipulating the font in the controls, they are only set initially through the settings in their properties (all textboxes use the same font). The problem occurs on multi-line as well as single-line textboxes. In debug mode, I can see that the font.size has not changed from the initial setting of 9, so there is no manipulation of the settings. It looks like the control is being zoomed, as the size of the vertical scrollbar grows along with the text. Closing the file and reopening it doesn't resolve the problem. The textbox retains the size that it has grown to. This problem seems to happen on some environments and not others, and I have not been able to determine the cause or factors that contribute to this phenomenon.
View 5 Replies View RelatedI am experiencing exactly the same problem as described in Textbox Text Increases In Size With Click here, so browse to the following location to view the sample file:
[url]
username "turbocam"
password "danielsresort"
Aloop I am trying to put into a macro. I have a variable range of data in Column C, header is in C1. I need to split the data in each row of the array using this code.
View 2 Replies View RelatedI have a macro (written with the help of forumites here) which, when selecting a cell on a particular sheet, will select and copy data from various rows on various sheets, print a resulting chart, before returning to the original sheet.
Now, I use autofilter on the original sheet to help organise the data I wish to copy and print.
My question: is it possible to adapt my macro so that, once it has run through once, will move to the next cell in the autofiltered column and run the macro again, and again, and again... to the bottom of the autofiltered column?
At the moment, I'm running the macro, pressing the 'down' key and then running the macro again until I've exhausted the column. There must be an easier/better way.
The below code outputs a value in the cell corresponding to alpha by drawing information from the range in issueRng. However it will only grab the value from the first cell in the range of issueRng. It grabs this cell value and outputs it in every cell corresponding to alpha. For instance if i have issueRng as the range from C6 to E6 (3 cells), then the code draws the value of C6 and puts it in the cell corresponding to alpha which for the first loop is Cell(7,6). However the code is putting the value from C6 into every cell instead of moving through the range of issueRng.
Dim alpha As Double
alpha = 6
Dim issueRng As Range
Set wb = ActiveWorkbook
With wb.Sheets("Issues")
Set issueRng = Sheets("Issues").Range(.Range("C6"), .Range("C6").End(xlToRight))
End With
For Each CELL In issueRng...............
I am a bit stuck in looping my macro. I have a working macro that does various functions for a period (say January) and I wish to run for periods 1 to 12. The cell "A1" has a drop down list (jan09, feb09 to Dec09).
Currently I select the month from Cell A1 and run the macro. Now wish to loop this macro so it runs from Jan09 to Dec09 in one go. The cell A1 is being refererence in the Vlookup table that changes ranges in a table that are been used as dynamic ranges in a macro.
Simplied Macro Steps
1. Select month in cell A1 (sheetX), then Calculate.
2.This refeshes lookup table and runs the macro say copies cells (B10:D100) in sheetBud and pastes in sheetTest A10. Insert rows A10:A100. Ends
Currently I manually changes the step 1 and then run this macro for next month. But now wish to run it for 1 to 12 months in one go. Please please can you can help me with the Looping. Please note that the macro does other functions and the steps above are simplied.
I have a single figure and as the figure increases and moves through thresholds the pay changes. e.g.
Number of sales payable = 15.66
Level 1: 1-10 pays 100 for each that falls in this threshold
Level 2:11-15 pays 150 for each that falls in this threshold
Level 3: 16-20 pays 200 for each that falls in this threshold
Level 4: 21+ pays 250 for all above this
I want to work the amount payable with a formula.
It should be $1849
Is there a formula that can calcualte this without me manually figuring out how many in each threshold.
I was just wondering if it possible to create a macro that permanently increases the height of a workbook by a set increment
I have several workbooks that i have the code below assigned to
[Code] ......
So when they open they resize to that set size and position. and what ive been doing if i need to resize (which is often) is just adjust the values in the above code.
Not that the way ive been doing it is difficult but i was thinking how great it would be if i could have a macro i could run that permanently adds 9 to the .Height and subtracts 9 from the .Top
What I'm trying to do: While there's a value in Col A starting with A2, I need to then go to the corresponding cell in Column O, starting with O2. Then as long as there's text in the next cell in the same row keep track of those cells so that in the end I can append all text in the adjacent cells to the text in the Col O[X] cell. Then repeat as long as there's text in the next row cell of Col A. I have the routine to append the text, I just need help with the looping.
IE:
If I start with the following:
Col A Col O Col P Col Q
row 1 ----- ----- ----- -----
row 2 1 Text here new text
row 3 2 More text more text 2 more text 3
row 4 (no text in any of this row.......................................)
After running macro this would become
Col A Col O Col P Col Q
row 1 ----- ----- ----- -----
row 2 1 Text here..............................
I have created a macro to filter results from a set of data, paste that data to a new file, and then create charts based on that data. Right now, my macro is basically multiplied 20 times to do this for each heading. Is there a way to create a loop that will just redo the same macro but for the next data heading? Here is a sample of the first part of the macro:
View 14 Replies View RelatedI have a macro I use cleanup up excel data I paste into it. Currently I paste in data that is three columns but there is space between the different items. For example:
View 2 Replies View RelatedA macro that scans Sheet "CSD" Range "B4:B201" for the word "Yellow" and copys the data in the row based on conditions to Sheet "Yellow Ticket" and prints the Ticket.
Heres a Pic of my "CSD" Sheet.
******** ******************** ************************************************************************>Microsoft Excel - Hybels_Loading_Tickets redo for post.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJKL1 Starting Load # 2 3800 3 Ticket ColorLoad #DropCart #Customer ALLSTARS4" Wave3-0-6 Wave6" Wave18-0-1 Wave10" HB4 Pink3851C1Hawks27 45 5Yellow3852D3Bomgaar's 28 67 6 Yellow3850E5Larry's 34 677 8 9 10 11 12 13 14 Count Sheet Esman Greenhouse's [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Here's a pic of my "Yellow Ticket"
******** ******************** ************************************************************************>Microsoft Excel - Hybels_Loading_Tickets redo for post.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGH1R. Hls Inc.2Helping Make Your Business Grow!3 4For questions regarding product please call 800..!5For questions regarding shipping or cart removal please call 888-344.3710!6 7 Load # Drop Letter Cart
#(1-30).....................
I'm running a simulation that utilizes the Data Table function. The table itself is quite large (280 elements in the row, 10K in the column). It currently takes about an hour to run. (Full disclosure: I did this instead of a couple of "for" loops because I have to consolidate/manipulate a bunch of data from several sheets and it seemed to be running slower than a data table). how to write a progress bar for this type of code? I.e. code that does not loop and is just time-consuming?
View 4 Replies View RelatedI want to use a piece of VBA to copy values from cells A1, C1, E1, and G1 and paste them into the next blank cell in an existing range called "DCopy". I am trying to use the following code, but as I have never tried writing a looping macro, have no idea what I am doing:
View 2 Replies View RelatedI have a macro that will go into a workbook and copy all applications from a given date onto a master workbook. I have another workbook that has a list of around 20 workbooks and i want the macro to go into each one, and copy over the data onto the master workbook. I have the code to copy it over but i can't get it to loop thru the list of 20 workbooks.
View 9 Replies View RelatedSo this code works fine without any error messages but I need to add a sum feature. Any ideas on summing up all the values from the for statement into the cell that corresponds to the value of the variable t. I think i need to store the the value of the for statement as a variable and then at the end of the for statement add the next value from the for statement and so on
Dim t As Integer
Dim m As Integer
t = 7
m = 11
Do While Cells(t, 7) ""
For m = 11 To (30)
col = Split(Columns(m).Address(0, 0), ":")(1)
Cells(t, 8).Formula = "=VLOOKUP(G" & t & ",indirect(" & col & 2 & "),2,FALSE)"
m = m + 1
Next m
t = t + 1
Loop
Sub highlight()
Range("AB6,Y6,V6,S6,P6,M6,J6,G6").Select
Range("G6").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=$D$6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub
the only thing is i can't get it to do it for the hole spreadsheet!
i could copy it on to each line! I have 105 .lines and it needs to be compared to a number that is on that line!
I have tried to use some
i = 1 to 105 ---- but i don't know what that means, where to put it or how to use it
just so you know the basic of why i'm asking this is
i work for a small tv station, as most media it has ratings.. I have to highlight the 15 minute blocks of time that we rank higher in!
so for example!
US - time frame - rating Them - rating
1st 15 minutes .05 1st 15 minutes .02
2nd 15 minutes .03 2nd 15 minutes .09
but i have like 8 other stations and a full 24 hour period i normally highlight
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit (n) (in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.
One of the settings in one of the sheets (B) dictates which building (n) is currently being looked at. As such, sheet (C) effectively builds a report for building (n) and so can report on just one building at a time, that building (n) being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).
If I want to get a consolidated picture for all buildings I currently manually change the building choice (n) in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).
I then need to manually change the selection (n) and then rerun macro (m1).
What I would like is another macro (m2), which automatically loops through the different values for (n) so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.
I had create a few macro with the macro name the same as the worksheet name.
How can i create another macro to loop through all the worksheet and if there is any data in Cell A1, it will call the corresponding macro. If there is no data, it will go onto another worksheet.
