Shortening Code With Loop
Jan 16, 2007
How would I shorten this code using a loop and arrays? It is working correctly but needs to be shortened down.
Sub cr()
Dim j
Cells(1, 1).Interior.Color = RGB(250, 155, 100)
j = 0.09
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.094
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.043
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.05
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.034
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.032.........................................
View 4 Replies
ADVERTISEMENT
Mar 20, 2008
How would I make the following code shorter/ more efficient? The columns don't vary much - either B, G or L. The rows vary a bit more....
View 9 Replies
View Related
Jan 17, 2007
I have put together some code which works fine but is very clumsy and, when it comes to expanding it in the very near future, will look really bad.
rn = Worksheets("Test"). Range("C1").End(xlDown).Offset(1, 0).Row
Worksheets("Test").Range("C" & rn) = Odate 'a variable already created
Worksheets("Test").Range("D" & rn) = Range("E8")
Worksheets("Test").Range("I" & rn) = Range("E4")
Worksheets("Test").Range("K" & rn) = Range("D6")
Is there a s way to simplify this code (and the several more lines of similar) using For..Next and/or With function(s)? I've tried a few combinations but can't quite strike the right one.
View 2 Replies
View Related
Jul 3, 2007
I have a combo box that is used very simply to select a column from sheet and copy that whole column down to where text ends into a specific column in another sheet. I.e whatever column is selected ends up in column B of this other sheet. At the moment I've been using If functions for each case but there must be a more efficient way of doing this; here is just a sample of my
If textseries1.Text = "Fund01" Then
With Sheets("data")
Range("C1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund02" Then
With Sheets("data")
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund03" Then
With Sheets("data")
Range("E1:E" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
There are about 75 columns in total but thought it would be a little repetitive to put it all up here!
View 2 Replies
View Related
Aug 11, 2014
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")
[Code]......
View 3 Replies
View Related
Sep 17, 2012
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
View 1 Replies
View Related
Jun 1, 2007
I have a very long column (almost 3000 cells) of URLs. I would like to replace them in the second column with shortened versions, basically cut them at the fourth slash.
how can I used functions in Excel to do it?
View 10 Replies
View Related
Apr 2, 2008
I'm using this function to make a short description of a product from a long description.
=MID(K2,1,200)&"…"
So it's taking the first 200 characters of cell K2 and adding a ... to it.
Whats happening is it is splitting words and ending at character 200 instead of the end of the closest word to character 200.
Is there a way to alter this formula to break at the end of a word close to character 200?
Or is there a better way to do this?
View 9 Replies
View Related
Jan 10, 2014
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
View 4 Replies
View Related
Feb 9, 2014
I have a letter and numbers in the A column. example: (A001_C108_1231Xb)
This kind of numbers go from Column A1 : A1800.
I need to print them out, therefore I would like to fill every page which has 50 rows and 3 columns : A, D and G to write in the data.
I tried to do it manually, which is quite some work.
View 5 Replies
View Related
Jul 15, 2012
shortening the below recorded macro?
Code:
Sub Macro1()
'
' Macro1 Macro
[Code].....
View 3 Replies
View Related
Apr 18, 2008
weekly i have a report that is generated that is over 5000 lines and 4 columns wide.
I currently copy and paste one page column to make it 16 columns across the page. Is there a function in excel to do this?
View 9 Replies
View Related
Mar 14, 2007
I need a macro that evaluates the range H66:GI66.
In that range the following variables could exist:
A3, A4, A5, B3, B4, B5, C3, C4, C5, D3, D4...all the way to I5 for a total of 27 different combinations.........
View 9 Replies
View Related
Jan 24, 2007
I have created 2 seperate VBA's that I would like to combine and improve upon. One removes Duffs, and the other eliminates blank rows. My goal is to do a copy and paste/special, then remove the duffs, and then delete all rows that do not have data in column H.
I have tried several VBA's listed on this sight, but cannot seem to get a VBA that does everything I need it to do, and do it quickly. I have attached an example of my spreadsheet.
View 5 Replies
View Related
Sep 25, 2009
I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.
=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5
From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula
*Note*
'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.
View 2 Replies
View Related
Nov 10, 2008
Loop with below code. I have the below code:
View 2 Replies
View Related
Dec 7, 2009
I just need to get it to loop down to the next row
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("B1:AC1")
For Each cel In rng
x = x & cel.Value
Next
.Range("a1").Value = x
End With
End Sub
View 9 Replies
View Related
Nov 15, 2013
I am trying to loop the following code for a total of 15 worksheets without copying and pasting that same code 14 more times for each worksheet. Right now it is only executing the code on the "CAN" tab. Is there a way to make it loop where indicated below?
The 15 worksheets are:
CAN
USA
ASG
Gallia
[Code]......
View 3 Replies
View Related
Feb 24, 2012
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rw As Integer
Dim LastRow As Integer
LastRow = Sheet8.Range("B16:B300").End(xlUp).Row
For rw = 16 To LastRow
[Code] ........
Cannot get the loop to run. I am trying to make it so the loop will stop when it reaches the last non empty cell.
View 6 Replies
View Related
Mar 20, 2014
i need a code that can get parameter like a date and put it in a cell in the same column but different rows .
View 8 Replies
View Related
Apr 5, 2007
I am trying get a set of code to run through the sheets in the workbook... All sheets EXCEPT 1 named "Summary".
How can I code the proper statement? This is my current
Private Sub cmdAddDistribution_Click()
Dim ws As Worksheet
Dim lCount As Long
Dim rFoundCell As Range
'check for selected cash flow
If Trim(Me.cboxCashFlow.Value) = "" Then
Me.cboxCashFlow.SetFocus
MsgBox "Please select a Cash Flow."
Exit Sub
End If...............
View 9 Replies
View Related
May 7, 2008
I have a print code that I want to run on a loop. In column A Sheet UPSLabels, I have record numbers 1 through 94.
I have a vlookup in "C1" of another sheet that looks at the record number in "B1" and pulls in the correct information from UPSLabels based off the matchin record number in column A. I would like my code to print, update the number in "B1" by 1, and keep printing the records until it reaches 94.
I've probably supplied too much information, but the point is, I need the loop to run until "B1" reaches 94.
Sub print_loop()
Dim cnt As Range
Set cnt = Range("L1")
ActiveWindow.SelectedSheets.PrintOut Copies:=cnt, Collate:=True
Range("b1") = Range("b1") + 1
End Sub
View 9 Replies
View Related
Apr 9, 2009
I am using Excel 2003 work PC, and when i run this simple code it stops during the loop, I have had this problem a bit its like something is hitting the esc key or ctrl - break. But no keys are being hit or are sticking.
I have closed Excel and created new work book pasted the code in but it still stops at r = r - 1, haven't done a restart yet.
Sub Macro2()
Dim r As Integer
r = 10
Do Until r = 0
ActiveCell.Value = ("Shut down in " & r)
Application.Wait Now + TimeValue("0:00:01")
r = r - 1
Loop
Application.Quit
End Sub
View 9 Replies
View Related
Feb 24, 2007
I enter 1 record using the form, the next records does not populated correctly. It looks like the code just keeps looping by ....
View 3 Replies
View Related
Mar 4, 2014
I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.
View 1 Replies
View Related
Jul 3, 2007
Im trying to make some code to go through a list of numbers, and pick the next highest number from the one entered in a form. Then I need to do some processing with that number (i need to create a worksheet with that number as the name, and place that number in a few cells on that worksheet, and the main worksheet, but thats all stuff i think i can do).
View 14 Replies
View Related
Oct 12, 2013
I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".
The data can contain up to 50000 rolls at times.
Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
[a2:a65536].SpecialCells(xlVisible).EntireRow.Delete
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........
Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.
User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10
Comments:
Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R
# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E
104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
[Code] ...........
View 5 Replies
View Related
Sep 3, 2007
I have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time:
Dim c As Long
Dim Limit As Long
Limit = Cells(Rows.Count, 11).End(xlUp).Row
For c = 2 To Limit
If Cells(c, 22).Value = "" Then
Cells(c, 22).EntireRow.Delete xlUp
End If
Next c
View 4 Replies
View Related
Apr 3, 2008
I have a requirement where I have to add a row with x columns using vba. this is the code I am using now
k = 17
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
Sheets("Products").Rows(k).Insert Shift:=xlDown
Sheets("Products").Rows(k + 1).Insert Shift:=xlDown
Sheets("Products").Rows(k + 2).Insert Shift:=xlDown
ThisWorkbook.Sheets("Products"). Cells(k, Prod6Col) = prodetails
ThisWorkbook.Sheets("Products").Cells(k, Unit4Col) = sSeats
isRecord = .NextRecord()
Wend
End If
In this code I have declared Prod6Col and Unit4Col as constants. instead I need to use them as variables like in this modified code. However if i use this code, I get an object definition error.
k = 17
m = 20 (20th column)
l = 18 (18th column)
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
View 4 Replies
View Related
Aug 14, 2014
I'm looking to use a do until/loop code to find a cell that equals a named range ("Clause") that is located on another worksheet, the code I have so far is:
[Code] ......
I've used something similar before and works, but I just cant get it to work. The values that I need to find the match to the ("Clause") cell are directly below the original activecell.
View 6 Replies
View Related
