Im having a bit of an issue getting my head around why I am getting the result I am. I have data in ws1, a search list in ws2 and the results of the search in ws3.
It works though for some reason it loops 3 times... not sure why.
Additionaly I want to be able to run the same sub against 2 work sheets, ie, ws1 and another ws titled "otherWS" for this example. So run the exact sub again and return the results to ws2 as well.
What I am trying to do is, (I'll generally explain it and will go in details when needed) I have four tabs, but right now I will focus on two tabs. The first tab is called DATA_INPUT, will be the raw data inputted by users, approximately 45,000 to 50,000 rows. The second tab is called DATA_SET has data that are already set from previous reconciliation, this tab has approximately 10,000 rows.
my goal is to do a search from data_input starting with row 3 all the way to the last row, and search to see if any of these data are in the DATA_SET tab. Vlookup will not work if any of you are thinking about this, b/c the datas are in phrase and the data_set data are in chunks of that phrase (i'll explain in detail if needed).
So far I've created a loop code that works and gets me the result I want, but the problem with the data's being so huge, the loop will be looping apprx 500 million times, this cause the program to run very very low. For example to run 1000 data from DATA_INPUT it takes apprx 30 minutes or more and sometimes b/c this is so huge it crashes. i'll already did the calculation of how many loop it has to run through to finish, it's about 500 million loops (50000*8000 = 400,000,000 plus looping through the phrase to find the word match, approx 500 million loops).
SO my question is how can I make my program run faster since it need to loop so many times, or if you have a better suggestion in solving this problem, let me know....
I am using the following code in a project and would like some help to loop it until either TRUE or #N/A is found in the active cell.
View 9 Replies View Relatedtrying to get macro to loop until there's no data to process.I have data in two columns A&B which it creates a chart from I then need it to skip C (which is blank) and do the same chart for columns D&E and so on indefinitely until there is no data to process..
I have attached a very simplified version of the data, the data is made up so the charts don't make sense but if you run the macro you'll see what I'm trying to achieve.
I have a worksheet and the products starts from the first row has product name and 2 following rows are other information which i do not need. i was trying to figure out a VBA code that can leave the first rwo and delete every 2 rows below the first one and then to complete this until the end of the data.
For example: in the following rows, i want to keep rows 1, 4, 7 and all others to be deleted and this process to continue until the last rows having data.
Column A
1 Product name
2 serial number
3 manufucture date
4 Product name
5 Serial number
6 manufucture date
7 Product name
8 serial number
9 manufucture date
Sub Deletesecondtworows()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
I have the following code that I use to create individual client files from one master spreadsheet. My problem is that it always creates and saves a blank file with only the 'rn' value in the file name. It seems that it is looping one extra time when the 'rng.value' list ends.
Sub MakeFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim criteriaRng As Range, usedRng As Range, rng As Range
Dim lh As String, ch As String, rh As String
Dim rn As String
[code]....
As a part of a much larger routine, I need a code to find five consequetive cells in a column with identical value "XYZ" and select the first one of them. Say,
I have a column:
apples,
pears,
apples,
oranges,
xyz,
oranges,
xyz,
xyz,
xyz,
apples,
apples,
apples,
xyz,
xyz,
xyz,
xyz,
xyz,
xyz,
xyz,
etc.
I need to select the twelfth cell in this column, highlighted red.
I guess there could be some complicated if-then loop to go through the whole column, checking each cell and comparing it to the next four, but it would take forever to excecute... Is there some other way around? To find the first occurence of five xyz's one directly under another?
I'm looking to use Autofilter and current region to select each persons data and paste to a new sheet.
The trouble is i'm not sure how to change the criteria name in the loop
e.g first name in list is Jack, next is John
Selection.AutoFilter Field:=1, Criteria1:="Jack"
I have a piece of code attached to a combo box. When selected the code loops through 3, even 4 times for no apparent reason. Has anyone come across this before? The loop can start either after the final "end sub" or part way through (.clearcontents mainly) Have included the code just in case it is something obvious.
Private Sub cmbDI_GrowthBasis_Change()
If UCase(Worksheets("Tables"). Range("GrowthNo")) = "NIL" Then
Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = ""
With Worksheets("Detail Inputs").Range("DI_GrowthRate")
.ClearContents
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Interior.ColorIndex = 15
.Locked = False
End With
Else
Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = Worksheets("Tables").Range("GrowthNo") & " :"
End If
End Sub
I have a peice of code (Below) and instead of going from work sheet 4 to the one in the workbook 2 from the end sheet by sheet running the same bit of code it just loops repeatedly on one sheet??
Sub a()
Dim n As Integer
For n = 4 To ThisWorkbook.Sheets.Count - 2
Columns("W:W").Select
Selection.Copy
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Range("L8:L9").Select
Selection.AutoFill Destination:=Range("L8:W9"), Type:=xlFillMonths
Range("L8:W9").Select
Application.CutCopyMode = False
Next n
End Sub
In this workbook Test1.xlsm under column "D" i have Po numbers. Some PO numbers are the same and some aren't. I have a specific code that creates an invoice for a customer when i hit the code however i must select a cell under column "D" in order for the code to create an invoice for the customer on that row. I usually create the invoice for all the customers of the same PO number. Sometimes the amount of invoices i have to create is overwhelming so i was wondering if it's possible that when i select a cell under column "D" that has, let's say, PO number "654" if the code can create an invoice for all customers that have the same PO number of "654"?
View 1 Replies View RelatedI want to execute the following code once for each day (Mon to Sun)
Dim Day As String
Dim DeletedDept As String
Set StartRange = Cells(Range("Cashiers").Row + 1, Range(Day & "_Date").Column + 1)
Set EndRange = Cells(Range("Cashier_Totals").Row - 1, Range(Day & "_Date").Column + 3)
Set EntryRange = Range(StartRange, EndRange)
For Each cl In EntryRange
If cl.Value = DeletedDept Then cl.Value = ""
Next
I was playing aroung some VBA code, but the code executes 98 times and I don't know why. For example, range A1 contains 5. When I type, say 3, in A1, I want A1 to show me 8 (5+3). Then I may type 10, in this case A1 must show 18 (8+10). And so on. I decided to place entered values in different column, then sum them in A1 with below stated code
View 3 Replies View RelatedI have a drop down list full of options for my user to choose. 3 of those options I have message box codes built so that if they are selected, the message box will pop up. However, the first one in the code makes the user click the "ok" box 3 times before it goes away, the second one makes them click 2 times, and the third they just have to click once. to make it where they only need to click "OK" once on each of those?
Here is the
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngIntersect As Range
Dim rngCell As Range
Dim sMsg As String
Set rngIntersect = Intersect(Target, Range("j24:j31"))
If Not rngIntersect Is Nothing Then
For Each rngCell In rngIntersect
If CStr(rngCell.Value) = "E Checking - 105" Then
sMsg = "Please have customer complete E-Statement Application for E Checking - 105 accounts"
Exit For
End If
Next rngCell
End If
I've taken data from a fixed-length text file and the records are identified using the first two characters on each line of the text file. I've managed to identify these in each row with other bits of code so that was fine.
Now, I have 56 string variables (identifying my record types), which I'm assigning into an array. Then, I want to go through each variable in the array, performing the same action by way of a For Next Loop where, by taking this particular RecIDNo (see my code) string variable I will get the code to copy/paste the data into the relevant worksheet (where later on I will apply a text to columns using another array). But first, this set must work before I can do that! To avoid confusion, note that the record types i.e. 01 through to 56, will be filtered, then copied into the corresponding worksheet with the same number.
The problem I'm getting is the autofilter I'm applying, then the array I'm trying to reference (to avoid repetitive coding!) doesn't work as the code doesn't compile (I've not worked a lot with Arrays but I'm learning all the time!)
Option Base 1
Sub test()
'The array is declared here:
Dim RecIDNo(56) As String
'The arrays are assigned the string variables below:
RecIDNo(1) = "01"
....
RecIDNo(56) = "56"
'This worksheet contains my data:
Sheets("DATA").Select
For RecIDNo(1 To 56) '<--I THINK THIS IS WHERE I'M GOING WRONG!!
'Filter and select arrays:................
Data extract from sheet to sheet through column header values, i have set of data on sheet1,2..loop end of sheet
i want pull column data from multiple sheet column values into masterdata sheet depends upon column header values assigned in masterdata sheet.. my code works fine! but i want pull data if column header repeats more than one also
SO_NBR this is one heading in multiple sheets i cant pull col values not more than once under if SO_NBR repeats more than once.
[Code] .....
Find the attachment mentioned as expected result sheets is my requirement : HEADER_UPDATES_AIO_$.xls
when I run the code, because I have used many "Sheets("SheetNameHere").Select", the user will see the sheets flicking backward and forward many times and to avoid confusion, I don't want them to think "what the hell!"
Is there a way where I can still activate the sheets needed, but to somehow run all of "the code" in the background so all the user see's is maybe a status box or something which says "Please wait..."
I am looking for a way to measure how long it takes to complete a sub routine in VBA. I have code which posts data to matlab and then calls it back. What I need to know is how long does it take to complete each subroutine.
create a code which will measure this? I need to measure the time taken in 100ths of a second.
Is there any simple code to complete this? I am pasting a sample of my code which is using Matlab as a COM server.
Dim Matlab As Object
Dim MReal(10, 0) As Double
Dim i As Integer
Dim j As Integer
Dim MImag() As Double
Dim value As Double
Dim RealValue As Double
I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
I thought I found a formula that would work, but it's not working. Each month I have to count the number of service tickets that have arrived between certain time ranges. They want to gauge during what times we seem to get the biggest batch of service requests.
6 am to 10 am
10 am to 5 pm
5 pm to 6 pm
6 pm to 6 am
The format of the cells are:
1:21:19 AM
1:28:08 AM
1:35:48 AM
1:49:19 AM
2:17:02 AM
7:14:38 AM
7:29:12 AM
8:08:28 AM
8:51:48 AM
8:54:19 AM
The formula I tried for 10 am to 5 pm: =COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))
It gives a result of 676, and I know from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.
I included a spreadsheet that lays out what I am looking to do, basically I copied some times and speed from net. It shows up in non 24 hour format. I need to find the times and an associated speed for each row (day) directly before and after my inputted desired time. I've searched for days, tried different formulas with index,match, lookups,timevalue, time, etc and even tried using other peoples vba code without success.
View 9 Replies View Relatedi need to calculate between time. If a person is working between 8 till 12 and then 1 till 5 i need to count the instances between these times.. so if somebody is working between these hours a 1 should appear and if they have finished their shift or are on lunch then this should change to 0
e.g.
8.00 | 8.30 etc.. 12.30 | 13:00
1 | 1 | 0 | 1
I know how to loop for 1 to 10.
But is it possible to loop for 01 to 10?
Like:
[Code] ......
Because I am pulling data from external aplication, so the 0 is fix in a row/column.. I will need to get the row/column for the whole "01" and not only "1".
I am not sure what I am doing wrong with this expression. I know the code is a little messy, sorry.
View 2 Replies View RelatedIs it possible to write a cell formula (not a macro) to loop through a range of cells and test for a value? Something tells me that I need to use the "Offset" function.
How do I continue to loop through the last row and get the values to 3 differrent text boxes?
If I remember right, I think the command is Offset, 1 to go to the right one cell correct?
Sub CommandButton1_Click()
'("PowerAnalysis").cells(Row, 3).Value = textbox1.Value
'Worksheets("PowerData").Cells(Row, 3).Value = TextBox1.Value
Set WkSht = Worksheets("PowerData")
LastRow = WkSht.Range("A" & Rows.Count).End(xlUp).Row
WkSht.Cells(Row, 1).Value = TextBox1.Value
'LastRow = r
'r = ActiveCell
'Dim r As Long
'r = ActiveCell.Row
'UserForm3.Show
'WkSht.Cells(Row, 3).Value = TextBox1.Value
UserForm3.TextBox1.Value = r
End Sub
This code is wrong, but how can I get the first three records on the last row to display in three textboxes on UserForm3 titled TextBox1, TextBox2 and TextBox3.
Norie may be right, I may not have to even think about using repaint.
I have created a spreadsheet used for timber design. The spreadsheet calculates whether a specified size of timber column will support a specified load under certain conditions.
The various conditions include, load duration, service class, strength class and limit states.
Ok at the moment the user specifies the load and length of the column, then chooses a size of timber and sets the conditions mentioned above. Once this has been done the calculations i have put together on another sheet, work out whether the size of timber can support the specified load.
It is important to know that the answer for the final strength formula of determining the strength of the column should be equal to or less than 1. If this is the case then the column is ok, if the answer is larger than 1 then the column isnt safe.
Also at various points within the calculation certain checks are required to make sure certain design values satisfy certain criteria. If they dont then a new size of timber sould be tried.
What i would like to be able to do is use a loop so that the spreadsheet will use differing sizes of timber and calculate the values that satisfy the criteria and eventually find the size of timber required. The final answer of the strength calculation should be as close to 1 as possible but not over.
My code is supposed to generate figures to plot the changes in a riverbed profile with time. At time zero, there is a main subroutine that goes through a number of Subroutines and iterations before ending. At time t, I have another main subroutine which is slightly different but would go through most of the subroutines of tine zero. I want the code to go through for time t=0, display the values and then go through the other loops as well, displaying the values for each loop. I thought I knew what I was doing, but the displayed results showed reasonable results for the first (time zero), but the others were just replicas of the same thing. Is there anyway I could use variables with vectors i.e patty(j,i), where i could run the loop for i, and then do Next j.
View 4 Replies View Related