Macro not responding

[TERRI LEE]

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

[arlu1201]

Are there many formulae in your file? If yes, then add this statement

Please Login or Register  to view this content.

after your last Dim statement in each code. And add this statement

Please Login or Register  to view this content.

before the End Sub.

Also it tends to happen that the files get corrupted after repeated use. So you can copy the contents to a new file, add in all the macros and see if the problem persists.

[RHCPgergo]

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.

[arlu1201]

Also, you can empty your clipboard by doing this

For the 1st code, replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

For the 2nd code - replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[TERRI LEE]

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

[arlu1201]

You can try the changes i suggested. If it still gives you issues, we can re-work the formatting code part.

[TERRI LEE]

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.

[arlu1201]

Try one code at a time. Change the entire code as per what i suggested.

[TERRI LEE]

Hi

I tried both and sadly they are still not working.

[TERRI LEE]

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

[RHCPgergo]

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

[arlu1201]

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

[RHCPgergo]

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.

[TERRI LEE]

How do i fix this

[TERRI LEE]

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

[RHCPgergo]

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

[TERRI LEE]

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

[RHCPgergo]

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

[TERRI LEE]

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.

[arlu1201]

In that case, you can put the color formats for the rows and then do the subtotals.

[RHCPgergo]

What arlu said, or you can use this code:

Please Login or Register  to view this content.

instead of this part

Please Login or Register  to view this content.

I made some comments to make it clear. Note that the code is not conditional formatting, it's just simple formatting.

[TERRI LEE]

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

[RHCPgergo]

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.

[TERRI LEE]

Hi

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

[TERRI LEE]

Please Login or Register  to view this content.

What does this actually mean in laymen terms.

[RHCPgergo]

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.

[TERRI LEE]

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

[TERRI LEE]

Please Login or Register  to view this content.

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

[RHCPgergo]

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"

[TERRI LEE]

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

[RHCPgergo]

Glad I could help.