Macro not responding

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi can anyone help me with these codes. For some reason they are now taking forever to run. I am not sure it even finishes because i cancel after about 45minutes.

Thanks

You are making a lot of copying, that's why it's slow. Try to copy whole columns instead of single rows one by one. Or instead of copying, just get the value.

Hi Arlu

I suspected it was corrupt because I had alot of computer issues this past week so I already tried to copy everything to a new file. Funny thing is that the macros would not run in workbooks that they previously did run in. I will try your suggestions now.

I just want to clarify the below:

I wanted to copy formula results from my initial sheets to the sheet that the macro makes. The two macros above are supposed to do this but I suspect I have done something incorrect here. On the one sheet when it did manage to run the macro it copied the formating for protecting the certain cells. i wanted it only to copy the formating that resulted in the red cells.

Perhaps instead of me coping the formating from the origanal sheets I should set the formating up within the macro so that it runs it on the macro generated sheet. (I would need help with this as I would not know where to start)

What do you think?

Terri

Should i try to make all the changes at once or first post two and then post 4. I have tried to do just post one and after ten minutes it is still not complete.

Hi

I tried both and sadly they are still not working.

When I terminate it it always debugs highlighting the following:

Range("A4:K" & lrow).Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(5, 7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

@ TERRI LEE #10:
try to exclude that line and see what happens when running the code. Maybe that's what causing the main problem.

Originally Posted by arlu1201

The code in post 10 is needed as she needed subtotals to be inserted in the file.

Okay, but first let's identify the problem and see what's causing the macro to be so slow. (I might have been wrong in post #3 that the copying took so long.)

After that we can move on to find an alternative code for the slow part. At least that's the logic I'm trying to follow.

How do i fix this

The macros ran fine until we added the lines coping the formats. If I take these out it runs fine.

In that case I suggest using the old macro and add some formatting code at the end.

Thats great but I am new to macros and what I have tried thus far has not worked.

If you attach a sample workbook that shows how it should look like, I can help you with it.

Please Login or Register  to view this content.

Hi this is what I have come up with. I need help with the following sections please:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Here i wanted a red fill in the cell if there is no text in cells in column C ( it should exclude the subtotal and total rows)

In actual fact all 4 conditional formatings in the macros should exclude the subtotals and totals rows.

Hi

If I use the code from RHCPgergo where do i insert it into my code. I also needed help as per post 19 with the two corrections that needed to be made.

Terri

It should look something like this. In the code I already made the two corrections - at least I think that's what you wanted.

Please Login or Register  to view this content.

Hi

If I want to insert your changes in the macro to run before the subtotals etc where would I insert it?

Please Login or Register  to view this content.

What does this actually mean in laymen terms.

Originally Posted by TERRI LEE

Hi

If I want to insert your changes in the macro to run before the subtotals etc where would I insert it?

Before this line I guess, but why do you want to do that? Doesn't it work the way you want?

Please Login or Register  to view this content.

About the meaning of this line:

Please Login or Register  to view this content.

Rng is a range variable. I use it in a loop so it always means the corresponding cell in column D. (D5, then D6, D7, etc.)
Rng.Offset(0,1) means the cell that is one cell right from the Rng cell. (E5, E6, E7, etc.)
Rng.Offset(0,3) means the cell that is three cells right from the Rng cell. (G5, G6, GE7, etc.)
Rng.Offset(0,1).Value means the value of the referenced range/cell.

So all in all it means what I have already written in the comments in the code: if value in column E is greater than zero AND value in column G is smaller or equal to zero then make the color of the cell in column G red.

(You could write the whole thing in one line, like this, the underscore (_) means that it's one line of code.

Please Login or Register  to view this content.

It does work fine in the driver analysis but in the other macro called truck analysis it seems to highlight the subtotals row in column C. i thi=ought if this was inserted before it runs the subtotals it would fix it.

Thanks once again for your help.

Terri

Please Login or Register  to view this content.

Sorry to be a pain but I'm still learning. What does this mean.

Originally Posted by TERRI LEE

Please Login or Register  to view this content.

Sorry to be a pain but I'm still learning. What does this mean.

Rng: this is a range variable in which I store a single cell. (For example A1.)
Rng.Text: the text value of a range/cell. (Let's say the value of the cell is "Profit Total")
LCase(Rng.Text): makes the text lower case. (In the example "profit total")
Right(LCase(Rng.Text), 5) : cuts down the right side of a string, 5 characters (in the example "total")

So the line means: "if the last 5 characters (lowercase) does not equal 'total', then"

Okay thanks now I at least understand what it is saying. I will be able to make the changes for the other macros where I want to insert this info. Thanks a million.

Regards Terri

Glad I could help.