# Posts by StephenR

• ## Finding exact text match

Try this. One of the Find parameters specifies an exact or partial match.

• ## Grab values from a range to different ranges meeting desired goals & then clear it

I hadn't realised you were already using Event code. I don't think I will be able to help. I can do (1) but doubt I can improve on (2) and not sure (3) is possible.

• ## Grab values from a range to different ranges meeting desired goals & then clear it

OK I'm not sure how your system will work so perhaps we can do a little test. This is not really answering your questions, but if it works I know the approach is broadly correct and if it doesn't work it will need a re-think.

Can you paste this code into the sheet module (right-click the sheet tab, View Code and paste):

Code
``````Private Sub Worksheet_Calculate()

If Range("A1").Value = 1 Then
Range("E2").Value = WorksheetFunction.Max(Range("B2:B10"))
End If

End Sub``````

When the sheet calculates, if A1=1 it should just put the max of B2:B10 in E2.

Let me know how you get on.

______________________

EDIT: if that doesn't work, can you try this code in the module of sheet "7" and see if it runs whenever I2 is changed.

• ## Grab values from a range to different ranges meeting desired goals & then clear it

OK that has shed some light.

Are you able to add these formula to the workbook or the thread here? I ask because I wonder if the values are coming from an external system as opposed to being generated within Excel.

• ## Grab values from a range to different ranges meeting desired goals & then clear it

So if E2 is the maximum of B2:B10 what goes in E3? Will A1 go back to 0 and then back to 1 and you put the next maximum in E3? How is A1 being updated?

• ## Copy Sheets from active workbook to new workbook and retain formulas

Think you can just remove that whole For loop.

• ## copy&paste every nth cell to another sheet

Try this (untested). Adjust sheet names to suit.

• ## Object required error when using variable in sub formula

Please use code tags in future as per forum rules.

Think you just need

Code
``wksTarget.Range("D19").Value = "=(B19-A19) + " & DaysToCompletion``

Anything in quotes will appear literally.

• ## Trigger based on maxima and minima

This site is getting painful so everything is hard work at the moment. (Can't attach a file either.) It works for me on your attached file with 0.8 / 1.1. I noticed that with some settings its possible that a trigger point is not reached before the next min/max is reached and that can cause an error. Should it automatically stop in those cases? The code runs for me from the button.

• ## Trigger based on maxima and minima

Works for me Art. When I run it I get

1.08295 in I30
1.07442 in J84

etc.

What values are you getting?

You can call the macros from each other so running one runs them all. (Also suggest you give mine a better name!)

• ## VBA transpose issues

Please use code tags in future as per forum rules.

I would move the workbook.close and two following lines to after the paste line. You are probably clearing the clipboard.

• ## VBA split sheets based on multiple condition and preserving format and column width

Keep your existing Copy line and add my code afterwards, keeping it on two lines as I posted it.

• ## Trigger based on maxima and minima

You can play around with the colours if you're fussed.

• ## Trigger based on maxima and minima

Yes, but that is an easy change to make.

• ## Trigger based on maxima and minima

Doesn't it relate to the maximum in row 147?

• ## VBA to copy CHECKED picture objects into EXISTING Powerpoint

"I plan to post this request in a few other forums, as well"

• ## VBA split sheets based on multiple condition and preserving format and column width

Code
``````.Copy
Sheets(s).cells(1).pastespecial xlPasteColumnWidths``````
• ## Trigger based on maxima and minima

If you take one of the values you didn't flag up, e.g. in I174, can you identify an issue there? Did you overlook it? If it's not right what's the logic?

• ## Trigger based on maxima and minima

OK, I've had a stab, though can't quite replicate the results. This code is adding a few more values than you indicated. The results are placed in column I, coloured green.

If can iron out the method, can then address speed if it's an issue, probably by using arrays rather than operating on the sheet.