how to set select case values from sheet 2 vba code

**sspreyer** · 08-26-2013, 02:38 PM

hi all
i have some code if any cell in column n has 133 entered it will pop up msgbox every time until 10 cells in column n are fill with 133 with can be offset by column f so if value 2 is enter then 133 message will only pop up 8 times i would like case value to be enter (133) on sheet2 a2 but would also like to set the lngCounter by cell b2 and if a value is enter in column f sheet 1 then take that way from b2 sheet 2 with in the code

i have mark some of code i would like to change
thanks for you help in advance

Please Login or Register  to view this content.

here a wookbook to test vba code.xlsm

**Solus Rankin** · 08-26-2013, 03:39 PM

Please Login or Register  to view this content.

**sspreyer** · 08-26-2013, 03:56 PM

you are a total legend mate it works but one more question is there away i can put it in the code if column f has value added in a cell that it takes the value away from b2 if 133 and b3 if 122 b4 if 111. For example if i put 133 in n4 and put 133 in a2 sheet2 and i put 10 in b2 sheet 2 i will get the msgbox 10 times
but if i put value 2 in column f say f4 i would like it to take it away from b2 sheet 2

thanks

shane

**Solus Rankin** · 08-26-2013, 04:06 PM

Please Login or Register  to view this content.

**sspreyer** · 08-26-2013, 04:23 PM

hi thanks sorry but this does seem to be working if i add 133 to a cell n4 and 133 in sheet 2 cell a2 and 10 in cell b2 sheet 2 and then i add 2 in to f4 sheet 1 it doesnt take the value from b2 sheet 2 the cell should change to 8 thank you i no im being a pain

cheers

shane

**sspreyer** · 08-26-2013, 04:29 PM

hi
Solus here a test work book showing what im trying to think it explains it better cheers again
test code 4.xlsm
shane

**sspreyer** · 08-26-2013, 07:01 PM

some how i need to add

Please Login or Register  to view this content.

to this line of code

Please Login or Register  to view this content.

would be grapeful with any help

this so if 133 is enter in any cell in column n and a value in column f is added then it takes it way from the ingcounter but i would like to take it away for worksheet 2 cell b2 aswell
please help
as this is my last pieces of vba project

**sspreyer** · 08-26-2013, 07:01 PM

some how i need to add

Please Login or Register  to view this content.

to this line of code

Please Login or Register  to view this content.

would be grapeful with any help

this so if 133 is enter in any cell in column n and a value in column f is added then it takes it way from the ingcounter but i would like to take it away for worksheet 2 cell b2 aswell
please help
as this is my last pieces of vba project
thanks in advance

ps. for administrator or moderator
i'm not try to duplicate posts but i keep get error saying do not have permission when submit my post so then i do it again then it posts then i see i have done it twice

**sspreyer** · 08-26-2013, 07:10 PM

bump up the list

**Solus Rankin** · 08-27-2013, 07:45 AM

What row in F do want to look for the number to subtract? The last row?

**sspreyer** · 08-27-2013, 11:27 AM

I would like it to look at same row that has 133 in it and takeaway the value enter in column f from b2 sheet 2
For example if column n has 133 and sheet b2 has value 10 in it and I put 2 in column f sheet 1 it will take it away from b2 value so then b2 will be 8 and then will only show 8 msgbox not 10 msgbox

Sorry for being a pain really thankful for your help

Shane

**Solus Rankin** · 08-27-2013, 11:50 AM

Please Login or Register  to view this content.

**HaHoBe** · 08-27-2013, 12:38 PM

Hi, Shane,

you should step back and think about your problem before posting multiple times as you discover something new to happen. This is the third thread (at least) about the very same basic problem, and I could have given you the code on the first try if you would have thought about that then.

Please Login or Register  to view this content.

Ciao,
Holger

**sspreyer** · 08-27-2013, 12:58 PM

you right Holger i just got abit carried away on what im try to get from the code sorry guys

The code you posted still does not subract the value enter in column f from sheet2 ingcounter value(b2) if i enter 133 in column n and put 2 in column f same row if i then look at orignal value of b2 in sheet2 the "ingcounter" i set at 10 i would like column f to subtract 2 from incounter vaule in cell b2 sheet 2 and should show 8 not 10 doesnt seem to be working

thanks for help and time guys

**HaHoBe** · 08-27-2013, 01:08 PM

Hi, Shane,

and why did you want a formula prior to that?

Please Login or Register  to view this content.

Ciao,
Holger

**sspreyer** · 08-27-2013, 01:36 PM

holger

i'm using the code for stores order workbook so a person can enter item number then it check asset area before order a new item for example when user enter value in f Qty of item and then enter item number in Column n it will tell them how many are in the asset area instead of ordering a new one

im still have issues with code if enter 10 in b2 sheet 2 then put 133 in column n it will take one away from b2 and show msgbox 9 remaining which is right but if i go next row down and put 133 in column n msgbox will say 7 remaining when should be 8 remaining and then sheet 2 b2 value changes to 7 when it should be 8

the code is very close to perfect thanks holger you are legend

shane

**HaHoBe** · 08-27-2013, 02:04 PM

Hi, Shane,

maybe it´s just

Please Login or Register  to view this content.

Ciao,
Holger

**sspreyer** · 08-27-2013, 02:51 PM

hi Holger
work better but if enter 133 in 5 rows message box pop up correctly but then when i get to 6 row and type 133 no message pop up even tho there is still 4 remaining in b2 sheet 2 say 4 remaining which is correct but the messages stops. its as if the code is counting every entry for 2 so 10 becomes 5 the last message box pops up before it stop working has got the correct count tho

thanks again

shane

**HaHoBe** · 08-27-2013, 03:01 PM

Hi, Shane,

you may find the solution by yourself when you set a break point at the Select Case and single step with F8 through the code.

Please comment the lines

Please Login or Register  to view this content.

and test again.

If the value of 133 should be in for 10 times and has been filled in 5 times cell B2 shows 5 - and when checking the code goes Exit Sub.

Ciao,
Holger

**sspreyer** · 08-29-2013, 12:27 PM

Thanks Lot i have finally got it to work i have ended up use a formula on work sheet to adjust counter value on the sheet2 and the vba code is adjusting the ingcounter so now the message box "show how many times " ties up with value on sheet 2

thanks alot guys for your help specially you holger -----

legend

-----
shane

here code so you can see

Please Login or Register  to view this content.

how to set select case values from sheet 2 vba code

LinkBack

Thread Tools

Rate This Thread

Display

how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Re: how to set select case values from sheet 2 vba code

Thread Information

Users Browsing this Thread

Similar Threads

Slow Case Select code

[SOLVED] Disregard case in VBA code. (UCase, LCase, Select Case)

Select Case code not returning correct statement

Select Case: Formatting the code properly

[SOLVED] Simplify Code - Select Case

Bookmarks

Bookmarks

Posting Permissions