I am creating a macro that needs to open a report copy the data to a new sheet, add the clients name and then loop. Although when it loops around for the second time it adds a new workbook, although I need it to append to the bottom of the first....
View 9 RepliesI am just looking for an function which will gives me the average of the hours worked on daily basis. A breif descripiton is i have an workbook in sheet1 i have the sample report which i struggle to prepare on daily basis for each client.
Sheet2 i have the Raw data in column H i have the client name and then in column I is Date and finally in J the hours we worked on in it. To say i just want the average if the each client on the particular date as shown in Report sheet.
My goal is to learn how to universally add specific codes to all clients in one shot.
Here's an example of the data I'm working with......
In column A - I currently have a list of 200 unique clients listed multiple times (1000 rows).
In column B - I have several different types of codes, but that are the same for each client.
Example:
A B
Client 1 Code A
Client 1 Code B
Client 1 Code C
Client 2 Code A
Client 2 Code B
Client 2 Code C
Etc. Etc. for 200 clients
So the question is: When adding a new code, How do I add a codes to all clients in one shot?
I have a few excel sheets that we use to keep up with clients and the hours spent working on projects for them. We add and delete clients almost every week, and every time we make these changes we have to update all of the linked sheets. I am wondering what is an easy way to have sheets update themselves as we add and delete clients.
View 9 Replies View RelatedI am actually using a basic formula to compute for the Staff OT based on the type of OT. see more details below.
JULY '09 (Worksheet1)
ABCBEFGHIJ5DateDayOT StartNo. of OT hoursOT TypeAttending EngineerRefDetailsCost6July 02, 2009Thu8:00 AM1111.00HolEngineer 1169504client support3,300.007July 03, 2009Fri6:00 PM11.00RegSystem Analyst169556support IT Manager, Albert's turn-overs assistanceneed formula8July 06, 2009Mon4:00 PM55.50RegEngineer 1169504midyear SR meeting supportneed formula9July 06, 2009Mon6:00 PM11.00RegSystem Analyst169556generate SIP data for the conf, setup SMRAS300.0010July 08, 2009Wed6:00 PM11.72RegSystem Analyst169556ZPC datat testing, attendforumsneed formula11July 09, 2009Thu5:00 PM22.50RegEngineer 1169504midyear SR meeting supportneed formula12July 10, .....................................................
I get this error on clicking a button in userform1 to display the userform2 (userform2.show) , i checked with another form and it works .
if i click on button to show userform2 second time , it displays outline of form and on third time it displays the form correctly .
I have a workbook that contains
Tab 1 --a datatable with mulitiple suppliers. Data changes once a week.
Tab 2-25 --Report Tab for each client--to be sent out to client weekly to update status of deliveries.
Current process:
1. copy and paste data table into excel model Tab 1
2. use advanced filtering to pull data from the datatable into each tab one tab at a time--25 times!
3. Sort the date in the report tab--25 times
4. Format the report tab--25 times
5. Copy and Paste into a separate workbook--25 times
6. Send each report--25 times
How do I reduce the number of steps in this reporting process?
I have a workbook that has multiple worksheets and I would like to use a command button to e-mail just one selected sheet. I want to create the single sheet as an attachment on a new e-mail message but leave the addressing to the user as it will vary, and not save the single sheet. My challenge is that the users have a variety of different e-mail clients (Outlook, Lotus Notes and others).
View 2 Replies View RelatedWith Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies View RelatedI have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
I am working on some code that loops through a column of number values. Whenever it encounters a number value and a blank cell in an offset column, it places that number value into an offset cell (forming a separate column to be compared to another column in a separate sheet). I would like to take all the values in that new column and begin placing them in a new column in a separate sheet adjacent to another table. Most of the time, these values should match the adjacent values in the separate worksheet. However, if they don't match, I would like a new row to be created for that mismatched value.
For example. This is the first worksheet. So far, my macro loops through the column with rows 1-5. It looks in the offset cell(0,2) for Isempty value and then places that value into the offset cell(0,6)
text
1
text
text
[Code]....
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")
For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c
i have a problem with a nested loop:
it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.
am i doing something wrong?
dan
==========================================================
Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster.
==========================================================
Sub new2()
Dim Org_Area As Variant
Dim Item As Variant
Dim Copy_To1 As Variant
Dim Cell_Ref As Variant
r = 1 ..................
Can't seem to figure out why my Do Loop keeps giving me an Error of "Loop without Do".
View 14 Replies View RelatedI have got a loop which is working fine but now i need another loop which will run till the end but need to repeat itself as soon the column x become 1 the highest number would be 3
here is my main loop A1 = 5000
and second loop need to run inside the this loop
Sheet1.Activate
i = Range("A1")
For b = 1 To i
If Cells(1 + b, 3).Value = "P" Then
Cells(1 + b, 29).Value = 1
Else
If Cells(1 + b, 3).Value = "S" Then
Cells(1 + b, 29).Value = 2
Else
If Cells(1 + b, 3).Value = "C" Then
Cells(1 + b, 29).Value = 3
End If
End If
End If
Next b
I have loops working in other loops. The macro is almsot working well. It does the calculation i want but it fails to stop a loop, because of that, the macro can't run the next main loop (c), which is to move to the next cell where the calculations must be run.
I attach a file. the troubleshooting macroation is Sub Itiration.
The code of this macro are bellow. Basically, the loop using d as counter run into an endless loop. I don't how to stop this loop without affecting the results which are calculated correctly.
Sub Itiration()
Dim CurCell As Object
Dim TempSum As Double
Dim d As Integer
For c = 3 To Cells(3, 4)
If Cells(11, c) > 0 Then
For i = 1 To Cells(10, c)
Do
Going through a loop, I am trying to load pictures into an image box (or alternatively into a label) one by one i.e. going through the loop the first time, I want to load picture 1, then on the second loop, picture 2 and so on. A bit like an automated slide show.
I have written a simple loop and have used the loadpicture function to load the picture into the image box. When the code runs, the image box only gets populated after the last run through the loop. I have tried using application.screen updating function and the image.activate function without success. It is a simple bit of code and I expect an easy problem to solve if you know excel vba well.
My code below goes into an infinite loop, with culprit line shown in bold and underline...
View 4 Replies View RelatedI am parsing 15,000 files from a network server. The files are all the same format and length. The problem is that the first few iterations of my loop run fairly quickly, 7 to 9 seconds a case, but after only 300 iterations I'm up to 60 seconds a case. How do I keep the last iteration running as fast as the first iteration? I've included the main loop of my parsing routine below.
Sub Fill_Summary_Tabs() 'fills out the Datapack and JMP tabs
Application. ScreenUpdating = False 'turn off screen updating for speed
Call PrepImporterTab 'formats the Importer Tab so that everything runs smoothly < 1 second
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject") 'part of the filename test
For N = StartingCase To EndingCase '***** Start of the Parsing Loop *****
Sheets("Setup"). Cells(20 + N, 1).Value = Time 'Print the start time of each case....................
This works to locate the first instance found:
View 11 Replies View RelatedI have managed to successfully use a do until loop, however once finished, I would like it to move to the next row and do the same thing.
View 9 Replies View RelatedI am having some technical difficulties trying to place data onto sheet2. Sheet2 starts out blank, as sheet1 is proccessed it pastes data to sheet2. I have tried a "For Each" that failed only pasting data into Range "A1" for every found instance.
In the code below, the area colored "Magenta", I need a Loop of some type that as data is pasted into column A on sheet2, it indexes to the next available cell and continues. How do I construct such a Loop or For Each with in the existing For Each?
I now wonder is it possible to add Text in the row above and below the loop can this be done? Like below?
Image
Test 1 Test
Image/
Image
Test 2 Test
Image/
I don't get For Each loops. All I want to do is cycle through everywork sheet in the active workbook and lock certain cells. I've got the lock part working right but I have no idea how to effectively preform this on each sheet. From what I've read I think a For Each loop is the best way, but I can figure out how it works.
View 9 Replies View RelatedI have a spreadsheet that uses columns A:I. I want excel to be able to look at a row in the spreadsheet and where column I is empty it should delete cells I and H, then look at cell G and if it is empty, delete cells G and F, and then do exactly the same for columns E and C.
also, this spreadsheet is not always the same length, but always starts at row 15