Re: Deleting entire row with empty "invisible" cells
This last one didn't work.. it says the variable searchformat is not valid..
I'm trying to work with the first macro I received. It does the job, but I have to run it about 10 times so that it removes all the rows based on the criteria.
Do you know why it doesn't remove them all? You can try with the attachment. It will leave some rows with blank spaces in column C and I have to run it again..
Thank you..
Alan
Hi.. I'm in a bit of a quandary here.
I need to transpose the contents of rows into columns, but I need a macro that copies cells B2:Q2, and pastes special tranposing in A2:A17, and then copy cells B3:Q3 pasting and transposing in A18:A33, and then on. It copies from sheet 13 and pastes on sheet 14.
I will apply a macro to delete the empty spaces afterwards.
Attached goes an example.
Thank you
Alan Andrade
Re: Deleting entire row with empty "invisible" cells
Thanks a bunch.. The only problem is that the macro takes too long to sweep through and it doesn't delete all the rows it should (it stops before deleting all the rows with the criteria). Is there anyway to delete all the rows below the last one that has data in row C? I'm saying this, because, right before deleting the rows I order them in ascending order, so there's no way I would be deleting important rows. All I need is to check for the last row that has data in both columns B and C, and delete all rows below them.
Thank you
Hi.. I've been having a bit of a problem with this sheet..
I know it sounds simple and I've searched for it in the forum, but it doesn't work in my sheet..
I need a macro to delete entire rows that have data in column B but have no data (apparently empty) in column C. Looks like column C doesn't have data, but I've tried to click on a cell a hit delete and tried the macro again and then it deleted the entire row. This means that there's actually something invisible in the cell, like a space or something, once those cells were copied and pasted special from another sheet.
Is there a way to ignore the fact that it has some "invisible" data, and delete the entire row with a macro anyway?
Attached goes an example.
Thank you
Re: Interactive calculation code
Maybe i used the wrong words, but the Data Entrega is the date the item has to be ready (customer's request). The "due date" I mentioned is supposed to be the date when the item is going to be ready considering that it is going to be using the machine no. 1. As some of the items have the same machine of preference, if I assign the no. 1 machine for all of them, some will only be entering the machine after the previous one has come out. Then I'm gonna have to calculate a new finishing date, accepting the fact that there's an item already being manufactured in that machine, and it has no other machine to be assigned to, and if that finishing date is later than the delivery date specified by the customer then the formatting is applied.
I'm sorry about the "due date" mistake. It's actually a "job finish date". Hope it clears everything out. Thank you.
Re: Interactive calculation code
Sorry, but the attachment had to be zipped..
The column F shows the number of days to be consumed by the machine to make the item so that Excel can return the due date in column K. But if the value in column K is greater than in column F it's a sign that the item cannot wait for that machine and it has to pick another. Column J had formulas that will not impact with the VBA or new formula involved, so I removed it. Note that cells that posterior due dates when compared to delivery dates have bold and red color formatted characters.
Thank you!
Hi..
I'm running a sheet that has a row dependant on another..
Let's put it like this:
Row K, has a value which is due date, and row H has a value which is machine to be used. Machine to be used has a range of 1 to 9, and due date, must be less than the delivery date. How can I fill in the machine in row H and read the result in row K, and if row K has a greater value when compared to delivery date row D, the macro will change the machine until it finds a due date lower than the delivery date. Briefly, the macro has to choose machine number 1 in cell H2 , calculate, read the result in cell K2, and if greater than D2, change value in H2. I need this to loop through the whole sheet until row 2000. Can it be done?
Thank you.
Hi.. I need to run a sheet that chooses a value with a combination of INDEX and MATCH functions, but depending on a value of a certain calculation it returns the value of that INDEX & MATCH formula, form a different column.
In the attachment, from the second row on, the formula looks up for a value that can be in either the 11,12,13,14,15,16,17,18 and 19th columns, of sheet "Banco de Dados". I'm using an If function to determine which one to choose, but Excel only allows 7 of those. The formula is:
[H5]INDEX('Banco de dados'!$A$2:$T$144;MATCH(A2;'Banco de dados'!$S$2:$S$144;0);IF(SUMIF($I$2:K2;ÍNDEX('Banco de dados'!$A$2:$T$144;MATCH(A2;'Banco de dados'!$S$2:$S$144;0);10);$K$2:K2)+G3/(21*'Cargas máquina'!$D$2)>1[/H5]
What I need is a way to add more IF conditions, maybe with a macro code, once I need to check if a value is greater than another and if so choosing that column will make it greater than another value than choose another, and from then on..So I think I need a looping routine..Thank you
Hi.. I know this sounds simple, and I've searched for this macro in the website, but it's not working and I don't know why.
I'm trying to delete the entire rows that have empty cells in column D.
It looks like it doesn't find any rows, or maybe there's something in the cells that I can't see, once they're copied and pasted special (contents) in that position.
Attached is the sheet.
Thank you!
Hi..I know this sounds simple, and I have tried to adapt the codes I have to do this, but it returns "incorrect type" when I run it..
I need to delete the entire row when the value in column AG is equal to zero. The code I'm using is:
Sub DeleteRow()
Dim c As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("AG65536").End(xlUp).Row
For c = LastRow To 2 Step -1
If Cells(c, 33) = 0 Then
Rows(c).EntireRow.Delete
End If
Next c
Application.ScreenUpdating = True
End Sub
But it doesn't work. Can Somebody help me, please
Thank You
Hi:
I Need to run a macro that checks for values equal to 0 (zero) in the column AG, but as that column contains a formula I can't delete the entire row beacuse it will delete the formula as well and it will cause problems in the future.
How can I delete only the rows from A2 to AF300 by checking for values equal to zero?
Re: Returning lowest value with non adjacent cells
Yes, sorry.. my Excel is in portuguese, so I never know what is the right word that describes the formula. Basically what I need is that the cell in column AG, and all of the other columns to right to "see" if there are more than a certain number of machines occupied, the item is gonna have to wait for the first one to free up, and that availability time would take place after some minutes that are represented in cells B3,D3,F3,H3,J3,L3,N3,P3,R3,T3,V3,X3,Z3,AB3,AD3 and AF3. I need a formula that tells the sheet, if there are more than (for example) 5 filled cells among B3,D3,J3,L3 and N3, I want to return the smallest value of them all, but these cells are not adjacent. And then in the next column I may need to return the 2nd smallest value, and then the 3rd smallest, and from then on.
Thank You
Alan
Hi,
I'm trying to run a manufacturing order in this sheet, but the function LOWEST, doesn't let me choose between cells that are not adjacent. In this sheet in column AG, there's a formula that returns the lowest number in one cell, if it matches a certain criteria. Attached goes the file. It means that, if there are more than 4 numbers (operations), using the machines, I'm gonna have to wait for the one which is available first, and then in the next column if there are more than 5 operations in the specified cells I have to wait for the second machine to free up.
Do you have any idea of how I can make that formula work?
Thank You
Alan
Hi.. I'm trying to repeat a certain formula but it will take too long if I do it manually. The attachment shows that in sheet 11 the first row has a formula like INDEX(FeXJun!$B$2:$Z$90;MATCH(INDEX(Fluxo!$B$3:$Q$50;MATCH(B2;Fluxo!$A$3:$A$35;1);1);FeXJun!$A$2:$A$90;1);MATCH(B2;FeXJun!$B$1:$Z$1;1))
What I need to do is to expand the formulas in row 2-21, than get the formulas from rows 2-21 and paste them from rows 22-41 but then changing the reference, instead of MATCH(B2;Fluxo!$A$3:$A$35;1), I need MATCH(B3;Fluxo!$A$3:$A$35;1), and instead of MATCH(B2;FeXJun!$B$1:$Z$1;1), I need MATCH(B3;FeXJun!$B$1:$Z$1;1). And that change needs to happen untili let's say B150, so I would have to do it manually 150X16X2=4800 times.
Is it possible to do it using a VBA or some formula trick?
Thank you
Hi..
I need to concatenate the values in sheet "Data", returning them with a formula in the first column of sheet 2.
The formula should look up for the value in column A of sheet 1 in sheet "Data" and see if the moulds are the same. If they are, it should concatenate the PN's as it appears in the attempted formula. If they are not it should return only the PN's that have the same moulds.
If they are all the same, then the 2nd, 3rd and 4th rows should be blank. If the mould 1 is the same as mould 3 and 4, it should concatenate the 3 PN's in the 1st row, but the 2nd row should come up with PN 2 alone, and then on..
Is it possible??
Or I would need a macro? If I wanted to write the formula like the one I tried, excel says it's too large.
Thank you
Re: Apply formula throughout the sheet
It worked out great man! Thanx a lot..
Re: Apply formula throughout the sheet
The thing is: There are 94 items that the cell A3 should calculate with, and the excel sheet won't take that many. So I need the macro. The formula will calculate the the need for "BATCH 01" contained in "00.410" (sheet 2), and multiply by the amount of "00.410" on march 1st. Then for the same cell, it will calculate the amount of "BATCH 01" contained in "00.415" (sheet 2), and multiply by the amount of "00.415" on March 1st, and then on, summing all the amounts in the same cell. Then it will pass on to the next row and calculate the amount of "BATCH 02" contained in "00.410" and then "00.415" and then on.. After that it will pass on to March 2nd, and it will start over, calculating the amounts of "BATCH 01, BATCH 02, BATCH 05,....".
Thank you.
I need to apply the following formula:
=VLOOKUP(A3;'2'!$A$3:$H$9;2;FALSE)*PROCV('Com 2'!A3;'Com 2'!$A$3:$AF$13;COL(B3);FALSE)+VLOOKUP(A3;'2'!$A$3:$H$9;3;FALSE)*VLOOKUP('Com 2'!A4;'Com 2'!$A$3:$AF$13;COL(B3);FALSE).
It will calculate the amount of "BATCH 01" in a material list and multiply by the amount of the needed material list to come out with the total amount of "BATCH 01" needed. I need to extend the fomula so that it calculates the amount of material list needed to the last row and keeps summing in the first A3 cell in the current sheet. Then I need to move to the A4 cell in the current sheet and do it all over. Notice that i added a "COL(B3)" locator to determine the column to search the material list demand, once it is classified by date. After it has completed the extension of that formula for column B in the current sheet, I need it to pass it on to the column C and do it all over again until column AF, always following the same pattern. How can apply a macro to loop through this whole thing? Do you think the "COL(B3), COL(B4)...thing will work? Thank you
Re: Delete entire row based on cell
Thanks once again!!
Re: Delete row based on criteria in the first column
Thank you so much man!!
