Macro to adjust values in columns

[additude]

I'll admit I know very little about creating a macro in Excel so I hope someone can help me out with creating what I consider a fairly simple macro to adjust values in a column or columns.

What I am using this worksheet for is a merchandise database that contains 3 columns of pricing values, ie. Column headers Small, Medium, Large.

The columns are approximately 2000 rows deep.

I need to change the pricing values in all 3 of these columns by a specific amount. However there are some values in the "Large" column that will not be adjusted. Those values not adjusted will be based on the contents of another cell in the same row that would only contain the data "OT,JB" or only "JB".

I've considered using find/replace but think it would be very inefficient.

Right now I am looking to lower the price values by 2 dollars. All values are numerical and formatted as Number/General. ie, 11.95, 7.95

I've considered creating another column or linked worksheet and forumla-ize it and then copy/paste my value results back, but I am hoping for something less cumbersome, something that is expandable as the worksheet grows, etc. I think a macro, but I do not have any idea what to do or how to get started.

I am not familiar with VB either.

Thanks.

[StephenR]

Does this help? Assumes your data are in columns A-C, data starting in row 2. Not sure where the OT, JB might appear so this checks anywhere in the row. To enter, Alt-F11, Insert > Module and paste the code.

Please Login or Register  to view this content.

[additude]

Thanks.

I changed the code to the right columns and ran it but it errored for a "Mismatch".

Please Login or Register  to view this content.

The error highlited "rng = rng - 2" as the source.

I'm not sure what to do.

Appreciate.

Added a "." to "& Cells" for "F2:F" but that didn't help either. Popup said a runtime 13 error.

And the OT,JB is in column I

[StephenR]

Perhaps you have non-numeric values in the range? Try this:

Please Login or Register  to view this content.

[additude]

There are some non numeric entries in the columns, so that was the problem apparently. Those non-numeric entries need to be there.

I ran the new code and it cycled thru fine, however no values were changed.

XXX->All the entries I could see remained the same values in those columns.
Never mind the above statement. I was looking at a worksheet we keep that is sorted differently. It appears it's working. I'll post back shortly.

[additude]

Seems like one thing or another...

I was able to use the macro to make the value changes by Alt-F11, then insert module.

My workbook has two pages to it, both of the same data, just sorted differently with the same columns F,G,H containing the pricing data to be adjusted.

When I run the macro, it only affects the first page of the workbook.

Even if I open the second page and run the macro, it will only affect the one page. Even of I swap pages by dragging the tab over to change the sequence.

When I save the workbook then open it to run the macro, I get an error message that macros are not enabled and reference the help to determine how to enable them...

Help.

[StephenR]

The previous code was explicitly referencing Sheet1 which is why it didn't work on any other sheet. The code below will go through each sheet so may need adjusting if you have other sheets you don't want amended.

Please Login or Register  to view this content.

[additude]

Cool.

I really appreciate your help with this.

After I Alt-F11 Insert Module and then run the macro and close the worksheet then re-open the worksheet, the macro fails because VB editor says "All Macros in this project are disabled, refer to online help or documentation...."

Any ideas? I do not have VB help installed.

[StephenR]

Not sure to be honest. If you go to Tools > Macro > Security what level is set there?

[additude]

I've set it to all the settings, even the lowest and still errors.

[StephenR]

Can you attach a cutdown version of the workbook?

[additude]

Do you mean to delete most of the two pages, then try?

[StephenR]

Well just to make sure it's not too big to attach here - the whole thing if it will work.

[additude]

I uploaded the file as .txt after changing the extension from .xls because it wouldn't let me upload .xls format.

Hopefully it will be useable if you rename it .xls

[StephenR]

You have to zip the Excel file. The text file is garbled.

[additude]

I think I figured it out.

When resetting the security level you have to click OK, then close Excel and then re-open it and open the workbook. Then Excel asks if you want to enable macros.

I click "Yes" and it works.

[additude]

OK,

Now hopefully one last issue.

It changes the values in the rows that column "I" has "JB,OT" and "JB" in anyway.

It is changing all the values.

[additude]

Here it is in zip....

Maybe you can find the "JB,OT" thing...

ThanksI am heading out. I will check back tomorrow.

Thanks for your help

[StephenR]

Just need to amend this line, it was showing "OT,JB":

Please Login or Register  to view this content.

[additude]

...Ouch...I see they don't have a kick myself in the butt icon here. I actually saw that in the first code you posted and made corrections...then spaced it out on this block of code.

Thats what I get for being in the 15hr of my work day....

Thanks a bunch for your help!

[additude]

Is there a way to use something in VB like pattern matching in Perl for the "JB,OT"? So for example, if the cell contains "spaceJB,OT" or "JB,OTspace" or something like that.

In Perl it would be like:
if ($a =~ \OT,JB\)

What about being able to remove all leading or trailing spaces in VB. Is there something in VB that will let me do that in a macro? The I could just run the macro to clean up any issues like that.

Thanks again.

[Dutchbeer]

This is a function I use for removing any characters from a string.

Please Login or Register  to view this content.

Endjoy.

[additude]

Sorry to say I discovered a new problem.

Still concerning the JB, OT condition.

Which ever of the two worksheets is active, the "JB, OT" condition functions properly and the values are not changed, however, the worksheet that is not active (Meaning not selected to be viewed) will still change the values in the "JB, OT" row price fields.

Any ideas?

[additude]

Thanks Dutch...

Can I "call" this function like a subroutine? If so how so in VB?

Thanks.

This is a function I use for removing any characters from a string.

Please Login or Register  to view this content.

Endjoy.

[StephenR]

Oops, missed something.

Please Login or Register  to view this content.

There's a difference between "JB,OT" (no space) and "JB, OT" (space). What are all the exceptions - can you list them or generalise? There are various options for this, you could use a worksheet function like Find or Search. To remove spaces can use the Trim function. There is also Regular Expressions, a parsing tool but I haven't used that before.

[additude]

I set all the text to "JB, OT" so it is consistant with all other same type text in the worksheet. So from now on the macro will key on "JB, OT".

The exceptions would be anything a user (myself) or someone after me that would add entries to the Workbook to make a mistake with a space. In particular, " JB, OT", "JB,OT", "JB, OT ", " JB, OT ", etc.

I am still having the problem that the macro will change the values for "JB, OT" if those values are in the "hidden" worksheet. The Workbook has two worksheets, both the same data just sorted differently.

If I have "Worksheet 1" in view, macro works fine for "JB, OT" referenced values in "Worksheet 1", but the macro will change the "JB, OT" referenced values for "Worksheet 2" which is not in view. Consequentialy(sp), if "Worksheet 2" is in view, then the opposite happens, "Worksheet 1" JB, OT" referenced values will change.

These values should not change in either worksheet, but they do depending on which worksheet is not to the front and being viewed.

Any ideas there?

[broro183]

hi all,

I saw this a wee while ago & have been having a play...

I've removed the need to loop through the ranges by using paste special with a tempcell (not sure if it can be done w/o this) & a helper column to overcome the variability of the text string.

I'm interested to hear if it is any faster than the looping technique for the full sized version of the file.
I think the main weakness of the below is the chance for errors around the autofiltering section.
- I've left some commented ".select" lines in the code for checking purposes but these can be deleted when you're happy.
- My use of a worksheet function requires that the ss calculation be on "automatic" but I haven't coded for checking this.

Let me know what you think...

Please Login or Register  to view this content.

hth
Rob

[additude]

That certainly works on both worksheets in the workbook and the popup is pretty cool.

It works fast enough. It seems as fast as the previous code, maybe faster, but that's just to the human perception. I think I'd need to clock it to compare.

Thanks.

After the macro completes, it leaves column "H" highlited, which is more cosmetic I guess.

In the previous code I was able to change values in column "H", the "Large" pricing separately, which is something I need to do.

Even ocassionally I would need to also change the values that reference the "JB, OT".

Is it possible to incorporate entered values in the "popup" for those two options?

Thanks.

I am uploading a reduced size copy of the workbook that has the previous code in it to identify the problem of that macro changing "JB, OT" referenced values in the worksheet that are not in view.

For example, if RR NAME worksheet is open and the macro ran, it will work properly and not adjust the value of H:25, but will adjust the value on Logo Num H:10. Consequentaly(sp), if worksheet Logo Num is in view and the macro is ran, the value at H:10 stays the same as it should, but RR Name worksheet value at H:25 is adjusted.

Thanks for everyones help. This is a good learning experience for me. For one thing, I wasnt aware that Excel was so manageable with VB and macros.

[broro183]

hi,

For example, if RR NAME worksheet is open and the macro ran, it will work properly and not adjust the value of H:25, but will adjust the value on Logo Num H:10. Consequentaly(sp), if worksheet Logo Num is in view and the macro is ran, the value at H:10 stays the same as it should, but RR Name worksheet value at H:25 is adjusted

I think Stephen's last post fixed your above problem when he amended...

Please Login or Register  to view this content.

to choose the correct cells by changing "cells" to ".cells", (w/o the dot the macro defaults to the active sheet whereas the dot tells the macro that it is for the cells belonging to "with ws"), as shown here

Please Login or Register  to view this content.

Yep, the sky's the limit for input boxes try this...
(Please note there is no error checking in this code).

Please Login or Register  to view this content.

hth
Rob

[additude]

Rob,

You were correct that Stephen fixed that problem. My oversight looking at the "JB,OT" and overlooking the .Cell

Thanks for the feedback and info on how the "." works.

I plugged in your code with multiple popups and that works great for me as it gives me the greatest flexibility for expansion and growth.

I started modifying your previous code, to a point, but was experiencing some issues with my modifications.

Thanks for this block of code. I'll digest it and work on understanding what I need to do for some error checking. Maybe some kind of loop back for bogus user input and run time errors.

VB looks a lot like JS to me. I'm not that familiar with JS. I guess I need to learn more about VB then.

Thanks for everyones help!

[broro183]

Thanks for the feedback :-)

Here's a couple of useful starting points for your error checking code:
(There are some useful links on the source site too)

Please Login or Register  to view this content.

Note, the use of the line continuation (space underscore enter) means that no "else... end if" is needed, or you could code an "else" clause in to let users know that no changes are being made eg

Please Login or Register  to view this content.

This one is not specific but shows an applicable principle for identifying responses:
http://www.automateexcel.com/index.p...no_message_box

hth
Rob

[additude]

Thanks again.

I must be doing something wrong. I think this should work, but it doesn't. If I have no input to the input box I get a runtime 13 Type Mismatch error.

Here is a cut of the code where I have added some error handling based on your references.

Please Login or Register  to view this content.

Can you tell me what I am doing incorrectly? I want to build on this, but guess I need to cross a few bridges first...

[broro183]

hi,

Sorry I think it is my instruction that's causing the problem - the mismatch error means that it is trying to make a variable something other than what it is declared as.
(& I'm learning as I go now...

In this case, it seems that Excel is trying to make the inputbox response be VBCancel (?) when it is declared as Long.
I've taken the easy (& probably not the best) way out of this by allowing all the inputboxes to error (using on error resume next) and adjusting the checking code within the main sub.
The other option is to declare all the inputbox responses as variants & make a couple of other changes within the code.


Experts,
As I've just posted in the MS Excel community, do you have any suggestions/recommendations?
Should "StrPtr()" be placed in the function rather than the main macro?

(Also, I have an idea that we could use a 3D array (something like Rangename, RangeAddress, InputResponseforRange) for each range to allow the use of a "for each" construct, but have no idea how this could be done/if it would be worthwile.)
MS community post:
tbc...
edit: http://www.microsoft.com/communities...&lang=en&cr=US

Suggested Code:

Please Login or Register  to view this content.

hth
Rob

[additude]

Thanks.

Is there a way to "reset" the error after it has displayed one MsgBox for one error of sma, med, lar because the message box appears multiple times.

I figure the "error()" is being processed for each iteration of updates for each of the 3 price columns.

I think reseting that "smaError()" or which ever one back to null would probably prevent multiple displays of the warning:

Please Login or Register  to view this content.

Got any ideas?

Hummm...well after a bit more testing it doesn't appear that my idea of what was happening holds true. I don't know why the "No Value was entered" MsgBox appears twice for each time no value is entered...

Another thought...the MsgBox appears once for each worksheet in the workbook. There are two worksheets in this workbook.

Maybe it would be easier to label the MsgBox "No value was input therefore no changes will be made to the ThisWorkbook.Worksheets sma range"...just a thought, I haven't tried this yet. Maybe it wont let ThisWorkbook.Worksheets display...

I'm working on something like this, but can't get it to work:

Please Login or Register  to view this content.

Onword and Upword...
I am close with this:

Please Login or Register  to view this content.

The ActivePage is the selected worksheet, but I need the name of the worksheet that the macro is processing. Something like "CurrentPage.Name" although that object hasn't worked for me...

But something like that....

[broro183]

you're so close!

The ActivePage is the selected worksheet, but I need the name of the worksheet that the macro is processing. Something like "CurrentPage.Name" although that object hasn't worked for me...

Change the section "CurrentPage.Name" to "ws.name".


hth
Rob