My sub is supposed to take the values from Sheet1 and paste them into 16x10 tables on Sheet2. However, for some odd reason, the loop is skipping some rows. If you take a look, there are 2184 values in Sheet1, but only 1368 end up in Sheet2...SKUs such as 518166,518167,510573,513746,513752 are just being skipped for no reason.
View 2 RepliesI was working on a project this morning (I actually got help here but this is more or less unrelated) and ran into a strange problem. It seems the For loop that I was using is skipping cells.
Was trying to figure out the problem using this macro and a list or random garbage.
Private Sub CommandButton1_Click()
Dim number As Integer
For Each Cell In Range("A2:A500")
number = WorksheetFunction.CountIf(Range("A:A"), Cell.Value)
Cell.Offset(0, 1).Value = number
If number > 1 Then
Cell.EntireRow.Delete
End If
number = 0
Next Cell
End Sub
Maybe someone who actually know something about programming can straighten me out.
I have a macro I run on thousands of rows of data which occasionally has incorrect line breaks that need to be brought up to the previous row. The macro works perfectly, however I'm trying to speed it up by skipping rows that do not need processing.
The macro inserts a check in column AQ to see if column A meets certain criteria. If it does not, "False" is entered in AQ, indicating the need for processing. As it runs now, the loop steps through each row of AQ to see if it needs further action.
I am trying to come up with a way for it to automatically just jump to "False" instances, skipping sometimes thousands of rows of blank cells that need no processing.
[Code] ......
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.
Sheet2 looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
[Code]....
Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob
[Code]...
Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.
PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.
EDIT:
So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).
I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.
Here is my failed formula:
=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
I'm currently working on a report and what I'm trying to do is get a Row of information to pull into 4 rows. My current formula looks like this:
=INDIRECT("'Paste SAP'!H"&IF(MOD(ROW()-1,4)=1,ROUNDDOWN(((ROW())+3/4),0)," "),1)
I change the bolded number to correspond to which row (1,2,3,0) but it's not functioning. I've done it with other but for some reason this one doesn't work. I've attached the template so you can see what it looks like. The problem is with the SAP Tab and the info from the Paste SAP tab.
I am trying to get excel to autocount starting in a7 and ending at a600, skipping 3 rows in between, and only gng from 1-10 then repeating and inserting a page break after the 10th line i have put an example in.
SAMPLE1.xlsx
I am working on an inventory sheet. Its probably only complex due to the amount of data. There are several hundred locations we service and these are in a single column, and then there is the equipment we use that is several hundred columns.
Each location uses 3 rows and the 1st needs the formula to equal 2 rows beneath it. i.e. cell E28 has the formula =E30. I basically need to fill down, but only put that formula into every 3rd row, as the two rows beneath this row need to stay blank, or show number i manually put into either.
The second part of this, is there a way once that is done, to just select those cells, and fill to the right all the way? Again, i do not need to fill the two rows under that one.
I have attached part of the sheet .....
The green cells are the ones that need the formula, and those will be copied down, and right. the others need to be left alone. I have been sitting here for an hour manually entering in the =XXX for each cell.
I'm trying to total up some rows that are put in a repetitive order repeat. I can do this, but the method I'm using is highly inefficient. Is there some slick equation I don't know about?
Example below:
Worker 1 - Double Time - each of these has 200 columns for different job codes
Worker 1 - Over Time
Worker 1 - Regular Time
Worker 2 - Double Time
Worker 2 - Over Time
Worker 2 - Regular Time
Worker 3 - Double Time
Worker 3 - Over Time
Worker 3 - Regular Time
...and so on to 1000 workers
Total Double Time - for each job code
Total Over Time
Total Regular Time
i am useing colmn a only for input,
starting from cell A5 to the end of A,
so as i input data starting form cell A5 the active cell automaticly moves to the next row A6, so i could input another number but after ten rows it should skip an extra blank row and start this all again
but thats not the only prob, starting from cell B5 should count every ten rows and give it a number starting from 1,2,3,4... and no number should be on the blank row.
a b
5 223 1
6 234 1
7 987 1
8 787 1
9 976 1
10 324 1
11 345 1
12 455 1
13 247 1
14 685 1
15 ......................
On the attached example there is a button called 'unsettled hedges', click this and it loads a userform. In this example, there are 10 rows of data that want a value entering into the 'returns' textboxes.
If you try to enter a value into all of the 'returns' textboxes and click the 'settle hedges' button then all the values from the textboxes should against the relevant row in column L of the 'unsettled hedges' worksheet and then each of these rows should be copied to the next available row in the 'settled hedges' worksheet.
This isn't happening though, instead, it is copying across every other row from the 'unsettled hedges' worksheet, leaving half of the rows still on the unsettled hedges worksheet.
im trying to copy a data--paste special--transpose--
i want this data to be copied in a manner, tht the 1st entry skips 3 rows and then comes the next value??
i have table in the left side and i want to get the results in the table in the right side skipping blanks row price with taking in consideration blanks are formula contain ""
[IMG][/IMG]
Attached is a sample spreadsheet. Please look at the spreadsheet will reading this post, as it would be rather confusing otherwise.
From this spreadsheet, I need to create two new spreadsheets (I assume one at a time is best). One (S1) will contain data from rows where Column C = "Regular Hours". The other (S2) will contain data from rows where Column C <> "Regular Hours".
The format of S1 (3 columns) is: Payroll # (Column B), code for the type of hour (this code I will need to write into the VBA code), the number of hours (D:G,Q:T,AD:AG), and the constant "N".
The format of S2 (4 columns) is: Payroll # (Column B), code for the type of expense (this code I will need to write into the VBA code), the dollar amount (I:P,V:AC, AI:AP), the project (Column C), and the constant "N".
The trick is each row on the new spreadsheets can only contain one hour/expense (otherwise this would be easy!). So, I need to loop to copy/paste the right cells, and need to skip over blanks, and need to end at column AP, then continue to the next appropriate row. As well, columns H, U, and AH are not used on the new sheets. I have another spreadsheet slightly similar to this with looping, but it is a delete type loop, not a creation one, and it doesn't have to have seperate rows like this does. I know this one is a bit big, but I know someone out there can figure out some looping code that can handle this.
My merged cells are not purely aesthetical - each merged cell (covering 6 rows in total, beginning at A3:A8) has colums and rows to the right for other details/formulae relating to that specific merged cell (which contains a customer account name).
The list I'm trying to reference is on a seperate tab - 'Projects' starting in cell A4. This list will have project names added to it, so I need my merged cells to reference the list, and subsequently pulling through all new additions to the list, rather than having to add them on a 2nd time.
Is there an easy way to revise the following code so that it imports ONLY the non-blank rows from files?
Sub ImportFiles()
Dim i As Integer, files As String, text As String
ChDir _
"C:Test"
Cells.Select
Range("A1").Activate
Selection.ClearContents
'Selection.QueryTable.Delete
Application. ScreenUpdating = False
i = 0
files = Dir("*.csv")
Do While files <> ""
Open files For Input As 1
Do While Not EOF(1)
i = i + 1
Line Input #1, text
Worksheets(1).Cells(i, 1) = text
Loop
I need to be able to auto fill, on sheet two,cell references for every 26th entry. Below is an example of what I would like it to be able to do with the drag, auto fill.
View 8 Replies View RelatedI have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
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.
This is something that's been bugging me for the last few hours.
I've written a fairly simple VBA
I have 2 columns of raw data. With
column one containing the document Names.
and column two containing the document titles
i.e
Column 1
Doc 1
Doc 1b
Doc 2b
Doc 3
Doc 10
Doc 11.5
Column 2
Manual 1
Manual 1
Manual 1
Manual 2
Manual 2
manual 3
Manual 4
I am looking to be able to reform this information on another sheet to look like this :-
Column 1
Manual 1
Doc 1
Doc 1b
Doc 2b
Manual 2
Doc 3
Doc 10
Manual 3
Doc 11.5
Manual 4
Now I have created an addional column on an other sheet which checks for the row number where a new manual appears. and I have made this look like the row I would like to insert.
The idea i have is to use the above Numbers and the row itself and insert it in to the formated report (working from the bottom though). With this doen for each manual name change.
the number of Documents per manual can vary....
how to write the marco for this
I'm currently trying to put together a rather large application to automate a release procedure.
Somewhere in a procedure I have the following
'previous code...
WS.Columns(8).Select
Selection.Copy
WS.Columns(1).Select
Selection.Insert Shift:=xlToRight
WS.Columns(1).NumberFormat = "General"
End Sub
The WS is defined in the beginning of that sub as follows:
Dim WS As Worksheet 'iSpares sheet
Set WS = Workbooks(FrmReleaseFlow.Caption).Worksheets("iSpares")
whereas the FrmReleaseFlow is a userform, active at that time, the caption contains the filename currently working on (in NAME.XLS format, no path), the file is open and the sheet iSpares exists...
The problem is that the code in above procedure gets executed to just that line and then the sub exits and the program continues in the sub right after this sub was called...
The weird thing is, if I set a breakpoint on the line WS.Columns(8).Select, then the execution halts there. If I then continue, the other lines are not executed. Breakpoint on the other four lines are never seen...
So the problem lies within that line WS.Columns(8).Select ...
If I execute this command in the Immediate window, while the code is halted on a breakpoint, it does work as it should...
If I enter anything in a cell on one work book I get calculating cell for about 3-5 seconds.
I don't have any formulas looking at the book. and I have deleted, renames, copy and pasted, exported to text and reimported and still it does it.
The thing that is odd though is that if I press return again it clears the calulation cells message.
I have a 1500 row spreadsheet that will change every month. It's set up in three columns Name, Amount, Total. The Total column is supposed to hold the total of all the rows with the same name field. I.e.
4445-8889 12
4445-8889 8 20
5598-7785 10
5598-7785 10
5598-7785 20 40
The problem is that the number of identical named rows is different for each name and changes each month. I want to build a macro that will out the correct total in the last column of the last entry with the same name.
I have a macro that uses user input to locate a file. If no file is found matching the user input, a message box displays informing the user. However, if a file is found that matches the user input, the file should simply open. If a file is found, the macro successfully opens the file however it will still display the message box saying no file is found. Since the message box is triggered by an error, there must be another error I haven't accounted for in my code.
Private Sub CommandButton1_Click()
Private Sub CrmFrm1_Click()
If Len(TextBox1) <> 12 Then
MsgBox "Incorrect Case File Number"
FrmNew.TextBox1.SetFocus
Exit Sub
End If
Dim MyCase
MyCase = Dir("S:AccountingProbationTest Files" & FrmNew.TextBox1.Value)
On Error Goto ErrorHandler
Workbooks.Open Filename:="S:AccountingProbationTest Files" & FrmNew.TextBox1.Value
Unload Me
ErrorHandler:
MsgBox "No file found for this Case Number." & Chr(13) & "Please proceed to Template.", 0, "No File Found"
Unload Me
Exit Sub
End Sub
to ensure the user ends up looking at UserForm5 after having closed the UserForm2 with the x sign.
It works just great - the user gets logged on and can use the UserForm2 but when he/she closes the form with the cross and tries to lo on aner using the UserForm5 - which pops up autmatically due to the above code, both of the UserForms get cloed (or hidden).
Private Sub CommandButton1_Click()
Dim popup As Variant
If ExecuteExcel4Macro("'D:[target.xls]Trg Info'!R1C255") _
= UserForm5.TextBox2.Value And ExecuteExcel4Macro("'D:[target.xls]Trg Info'!R1C256") _
= UserForm5.TextBox3.Value Then
With UserForm5
.TextBox2.Value = ""
.TextBox3.Value = ""
.Hide
End With
UserForm2.Show
With UserForm2 .....................
I have come across this only a couple times and have tried in vain to find out what the "--" does in formulas
one example I have run accross in the forums is below
= SUMPRODUCT(--(ISNUMBER( FIND("SMS",A1))))
Also, is there a good refrence source for formulas like this?
I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.
Here is the original code from the previous post.
I've coded a macro that can randomly sample out rows of data from a sheet. This macro is used to sample out certain rows from a master sheet full of data (which has 48 columns) based on user names (a column titled LAST_UPDATE_NAME). For Example (ref. wkb attached), if for 5 users named Alastor, Catherine, Emma, Julie and Victor for whom i've to choose data from the Dump sheet.
I need to design a module in which if i input the no. of samples to be chosen against each name, then that many number of rows has to be chosen for that particular name and copied to a new sheet (probably such sheets can be renamed with the usernames). Also i want to consolidate the copied data from the multiple sheets and consolidate them to one sheet.