help automating manual code

[tadd9]

Hi guys, i've been trying for 2 days now to automate some manual code i have, what i mean by this is...

I have this code which i have written...

Please Login or Register  to view this content.

(Not sure why my copy paste of the code added ** but they are spaces in my code not *s, sorry)

Which does its job perfectly (even though it may be very messy and more complicated than needs be)(not sure if i need all the on error resume next lines though, they are there because when i altered other information on the sheet or deleted other information i got an error (type 13 mismatch irc))

So the issue im facing is that with this above code i appear to have to manually change the value of column 17 to e, i, ei, or blank for this vba to trigger...

I would like to have it so that this vba will trigger (unlocking and locking cells and removing or entering formulas accordingly) if a formula in column 17 is changed OR if i manually change it...

I copy and paste many rows and columns of information at a time which changes many formula values and in turn changes the formula in column 17 to show the value of e, i, ei or blank...

I also delete rows and columns of information and would need it to revert back to the state it was in (meaning do the command that is associated with a "" in column 17...

I would also like to be able to manually override and enter my own letter or blank in column 17 and have the correct command run...

I do appologise for just dumping my excel issues on you all, if anyone could be of assistance that would be great...

I have previously tried this code...

(Again sorry for the ***s they should be spaces)

Please Login or Register  to view this content.

And many other lines of code, learning as i go, to get this to work but as i said for 2 days now ive failed miserably

(i should mention that i am a bit of a noob to excel and have only been using it for a few weeks... and gotten myself in way over my head, doing things i didnt even realise it could)

Today being my birthday ive downed tools and admitted defeat... for today at least... but if one of the great wizards of excelforum could help me to see where i am going wrong it would be greatly appreciated...

As i said my first bit of code works great and changed range T and R exactly as i need when i manually enter the information...

Unfortunately when i then put a formula in column Q (column 17 in the code) the vba wouldnt trigger...

Since then i have had endless loops, multiple errors, excel crashes and all sorts of wonderfully annoying issues...

My code attempt above caused an loop and stack error and other code just didnt trigger...

I should mention i will require this code to be monitoring column 17 (Q) for any changes i make or the formula value shows and changing the same row column T and R according to whats in Q...

Thank anyone from the bottom of my heart if they can assist me here, it would be a great birthday present :D

Thanks for reading guys.
Regards

[ragulduy]

You either need to apply it to the worksheet_calculate event or test the cells that are changed manually to make the formula update in the worksheet_change event.

[tadd9]

Hi, thanks for the quick response...

I have played around with the worksheet_calculate() sub but i couldnt seem to get it to work... i fear that my manual code requires me to be targeting that cell in column Q to trigger... if a formula updates in column Q while i am selecting another cell it doesnt seem to trigger, even in a worksheet calculate...

I may just be missing something simple, i appologise.

[tadd9]

Sorry for the double...

I realise i could tie my column Q to update via a button or something but i would prefer it to trigger when i paste my information into the other columns of different rows.

Thanks again anyone who can see what i am missing... most likely a lot)

Edit: i should also add that it is rows 7 to 1006 that have the cells i am using and from column A to U... i obviously only need Q, R and T though

Edit 2: since i am copy and pasting and deleting from the rows that need updating, could i not tie that in to selected.rows and Range("Q").Value?

My only issue then is if i copy paste multiple rows and columns, will i get the stack error and will the vba know which cells are e, i, ei or blank (in order to determine what the cells in R and T column do of the same row)

All very confusing, sorry guys, i hope im over complicating it but i just cant seem to get the pieces to fit)

[tadd9]

No takers?

On my birthday aswell...

[Hawkeye16]

From my experience you need to put the Worksheet_Calculate() Sub into the sheet that it pertains to, not just a module. Can you try this and see if it works? If so then you can just start your code with the conditions you have stated and if they are not met exit the subroutine.

[ragulduy]

I would suggest uploading the workbook (or at least an example of it so we can get the gist), get rid of any sensitive/superfluous data so we can see what the calculations are doing and what they look like. It would make it much easier to offer a solution that way.

To upload a workbook, click go advanced and then attachments.

[tadd9]

Ok guys, i will attempt to make a sample asap, i fear it might break some formulas though but you will get and idea of what is happening...

And @hawk... i do have it on the sheets code, not in a module...

As i said i believe the issue with calculate() was that in my code i have to be targeting that particular cell for it to trigger and it uses my current selection as the target sort of thing...

As i said i will attempt to upload a sample as soon as i can, unless anyone can offer something simple i am missing?

How would i got about making the code apply to multiple rows that i have selected but any column?

Thank you for the help guys

[ragulduy]

The calculate event will occur whenever the sheet is calculated, it doesn't matter what cell is selected/targeted (you don't use a target arguement with it. So the downside is that the code will run whenever a calculation occurs and you can't really target specific cells with it (unless you create a dummy sheet to hold the previous value so you can tell if it has changed.

Generally, if you can trace where the formulas go, it is more effective to use a change event on the cell that is changing whcih triggers the formula update in the target cell.

[tadd9]

Ahh i see... well the change that is being read by other cells is to column F...

So when i copy and paste the cell/s i am editing are the ones in F... i also paste to other cells at the same time but F is the cell being watched by a formula in P and what every P shows then creates an e, i or ei or blank in column Q which i would like to trigger the VBA for the same row that i wrote up there... lol

Sorry for the long windedness

And thank you for the quick responses

[tadd9]

I think i just realised what i need haha

Could it be as simple as...

If target.column = 6 and target.value <> "" Then
If range("Q" & target.row).value = "e" Then
....my code....
End if
End if

?? Will give that a test and report back when i can get to the laptop.

thank you for the advice guys

[tadd9]

Ok guys well update is that it works... but now only works if im selecting a cell in column F... and it doesnt seem to work if i paste to multiple cells

[tadd9]

Sorry guys still no answers?

I am basically now just looking for a way to have the vba change the cells of more than just one row...

If i was to paste say 100 rows in one go, i would need the vba to run on each row and do one of the 4 options i coded (unlocking and locking and entering formula or removing formula)

Sorry everyone, any help would be very much appreciated

[tadd9]

Ok guys sorry for keep double posting and things but this is the code i am now working with

Please Login or Register  to view this content.

and as said it is working fine but if i copy and paste to more than one row at a time it only runs for the first cell in column Q... is there any way to have it run on all rows i have pasted to?

Sorry to keep requesting answers i am at a dead end though and have searched and searched but feel i am so nearly there haha...

I have also tried the line...

If range("A7:U1006" and target.row) <> "" then
... my code...

But it just causes a recurring loop and crashes or i have to end task or i get a stack error when doing things that way...

as i said is there a way to have my VBA above run on not just the first row but all that i have pasted to? Even if it has to run over and over for each line i can wait a few seconds LOL

thank you again guys, sorry to be a pain in the buttocks

[ragulduy]

Like I said, you need to upload a workbook. I think there a few solutions here so depends on what your workbook is as to which would be better.

[tadd9]

Well i have actually attempted to create a sample but i didnt realise how much everything relies on each other... if i was to upload the workbook without all the sensitive material i would just end up with a column in Q that no longer is updated with whats in column P and would break or #REF most of the formulas...

I know its a pain but i really am just looking for some sort of command to make this code loop or re run down the column for each row that was changed...

Any information needed i can provide, but it is as simple as me pasting information from cell A7:O1006... then from p onwards those columns update according to the information in column F...

Sorry for not being able to provide a sample but as i said i just get ref errors in my formulas and creating a new workbook based on my current would just end up being A updates B which updates C which my VBA would change D and E...

I basically just need some code to make my code run on more than the first line...

I have attempted selected.row instead of target.row but again i got a loop...

Manually entering the e, i, ei or leaving it blank is looking like a good idea at the minute thought this would be a quick fix and a simple command i missed but getting more and more complicated...

Is there no way of having some code wrap around my code that makes it re run down to the last used cell?

Thanks again if anyone can help me

[ragulduy]

I can't solve your problem without seeing at least a basic representation of your workbook unfortunately.

I've attached a sample worksheet I created showing two methods for updating a cell which is changed by formula. In my example, A1:A3 are coloured red for <15 and yellow for >=15. There are two sheets, one uses the worksheet_calculate event and one uses the worksheet_change event.

[ragulduy]

Actually, I just re-read one of your previous posts. To apply code to each cell in a target range in a worksheet_change event (such as when you paste multiple values like you say) you can do something like:

Please Login or Register  to view this content.

[tadd9]

Ragulduy...

Will you marry me?

Haha sorry my friend but you have just made my day...

*runs to the laptop to test*

I am not all that good with VBA at the minute so not aware of all the capabilities it has... this looks to be my solution though... as i said my code now works... but it only sees the target as the first row and doesnt re run for each changed row...

With this code you just gave me though i can see that it will now re run my code for each "cl" or cell i am guessing in that range...

My issues have been that it just loops and runs on all 1000 rows and never stops, but hopefully this code will make it run on just the changed cells and then end...

Testing now, will reply with outcome, thank you again, friend.

Regards

[ragulduy]

No problem, you are correct that it will run for each "cl" in the range (note - I used cl instead of cell as cell is a "reserved" word in VBA for somthing else, it's better to use variable names that do not conflict with other things!)

[tadd9]

Now it doesnt run at all ahhh *pulls hair*

I hope i am just writing it into the wrong place or order...

Please give me a little time and i will have a play around to see if i can get it into the right place and working correctly... sorry had a day off yesterday so lots to do today.

Thank you for the help so far...

If you could maybe offer some pointers on how i should combine it with my code it would greatly appreciated...

I should add that your line of code...

If cl.column <> 6 then

didnt trigger the vba, but putting <> 1 made a loop and a crash in excel... sorry thus was all i could test, will get back on the laptop asap and attempt to get this working.

Thanks again friend

[ragulduy]

Try this:

Please Login or Register  to view this content.

[tadd9]

That works very nicely!! Thank you thank you thank you...

Just one issue now lol if i delete information, meaning everything reverts back to blank... then it doesnt do the option for if range Q = ""

thank you for the code so far though, works beautifully, had to add a * to the formula in the code but that was a quick fix... i assume you removed all *s as my copy paste of the code had weird *s where spaces should have been lol

[ragulduy]

Please Login or Register  to view this content.

The <> "" part will mean it doesn't run if you change the cell in column 6 to blank. If you get rid of that bit it should be ok.

Note - you might be able to speed up the performance a little to check if anything in the target range is in column 6 and then only looping through the cells in column 6, something like:
Something like:

Please Login or Register  to view this content.

And yes, good spot, I did find and replace to get rid of all the *'s, didn't realise I'd remove the multiplication ones as well.

[tadd9]

ok removing the "" give a syntax error and removing the <> "" part doesnt give an error but doesnt run after delete either...

testing the intersect method now, although performance isnt an issue for me, if this works that is all i wish... thank you again, i am learning some good stuff here

[ragulduy]

I meant, instead of:

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

[tadd9]

ahhh that works... so does the intersect method...

well that is me done... i shall reach for the solved and add rep button...

i cant thank you enough friend...

the option i have settled on was intersect method as you said this could improve performance and my code is now...

Please Login or Register  to view this content.

this also worked though...

Please Login or Register  to view this content.

Again thank you so so sooooo much, forget mrexcel or stackoverflow this is the place to be for excel answers and help and great community :D

awesomeness!!

[ragulduy]

Thanks, sorry it took so long to get there!

[tadd9]

Not a worry, it was my pace that slowed us, thank you my friend!!

Regards