New to VB for excel..

ternelson · Jun 13, 2018

Hi All,

So i just started learning excel and the vb part of it, im getting some of it but not all of it... what im trying to do is take a single word like blue and have it add 1 to a cell that has a number... ie if i have 15 in B15 and then change H2 to blue i would like it to add 1 to 15 to make it 16... or have it add it to 2 different cells.. ie if i have 15 in B15 and 12 in B19 and change H2 to red that it would add 1 to both B15 and B19... hope i have explained it right... thanks in advance for any help..

Terry

ternelson · Jun 15, 2018

thank you ... sorry its taken so long to get back to you was a busy day .... i'll have to test the coding out tonight ... im glad its friday .... thanks again for the help...

jproffer · Jun 15, 2018

No worries on the time taken. I don't (or I try not to) live on here either...occasionally they like it if I do something productive for them

.

ternelson · Jun 15, 2018

Not expecting you to answer this on the weekend but when you get a second here is the code i entered.. this is the 2nd half the 1st half works fine any thoughts on what i did wrong?? if i run this code without the first half of the code it works fine...

Code:

      End Select
    
     Case "$O$6"
         Case "Y"
            Range("B19").Value = Range("B19").Value - 1
            Range("B23").Value = Range("B23").Value + 1
         Case "A"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
         Case "M"
            Range("B15").Value = Range("B15").Value - 1
            Range("B19").Value = Range("B19").Value + 1
            Range("B21").Value = Range("B21").Value + 1
            Range("B23").Value = Range("B23").Value - 1
         Case "O"
            Range("B15").Value = Range("B15").Value - 2
            Range("B17").Value = Range("B17").Value - 2
            Range("B19").Value = Range("B19").Value + 1
            Range("B23").Value = Range("B23").Value - 1
         Case "V"
            Range("B15").Value = Range("B15").Value - 1
            Range("B17").Value = Range("B17").Value - 1
            Range("B19").Value = Range("B19").Value + 1
            Range("B21").Value = Range("B21").Value + 1
            Range("B23").Value = Range("B23").Value - 1
            Range("B25").Value = Range("B25").Value - 1
            
              
End Select

End Sub

jproffer · Jun 15, 2018

Post both halves

...much easier to debug when you can see the whole routine. Sub (Name) to End Sub please.

Also, you say this half works by itself and the first half works by itself, but together they bug out? If so, which line is highlighted, and what is the error message?

ternelson · Jun 16, 2018

sorry about that wasnt thinking there could be an issue with the first half ... i dont get an error thrown or a highlighted line thats the odd thing..

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$H$2"
        Select Case Target.Value
            Case "Hu"
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$A$231:$A$232"
             Case "Ce"
                Range("B15").Value = Range("B15").Value + 2
                Range("B23").Value = Range("B23").Value + 2
                Range("B25").Value = Range("B25").Value - 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$57:$D$62"
              Case "Dw"
                Range("B23").Value = Range("B23").Value + 1
                Range("B25").Value = Range("B25").Value - 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$46:$D$53"
              Case "El"
                Range("B17").Value = Range("B17").Value + 1
                Range("B23").Value = Range("B23").Value - 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$12:$D$30"
              Case "Gl"
                Range("B15").Value = Range("B15").Value + 1
                Range("B23").Value = Range("B23").Value + 1
                Range("B25").Value = Range("B25").Value - 3
              Case "Gn"
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$31:$D$40"
              Case "Go"
                Range("B17").Value = Range("B17").Value + 2
                Range("B25").Value = Range("B25").Value - 2
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$54:$D$56"
              Case "HG"
                Range("B17").Value = Range("B17").Value + 1
                Range("B25").Value = Range("B25").Value - 1
              Case "HC"
                Range("B15").Value = Range("B15").Value + 2
                Range("B23").Value = Range("B23").Value + 2
                Range("B25").Value = Range("B25").Value - 3
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$A$231:$A$232"
              Case "Hl"
                Range("B15").Value = Range("B15").Value - 1
                Range("B17").Value = Range("B17").Value + 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$41:$D$45"
              Case "HO"
                Range("B15").Value = Range("B15").Value + 1
                Range("B23").Value = Range("B23").Value + 1
                Range("B25").Value = Range("B25").Value - 2
              Case "LM"
                Range("B15").Value = Range("B15").Value + 1
                Range("B17").Value = Range("B17").Value + 1
                Range("B25").Value = Range("B25").Value - 3
              Case "Mi"
                Range("B15").Value = Range("B15").Value + 2
                Range("B23").Value = Range("B23").Value + 1
                Range("B23").Value = Range("B23").Value - 3
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$63:$D$68"
              Case "WF"
                Range("B21").Value = Range("B21").Value + 1
                Range("B17").Value = Range("B17").Value + 1
                Range("B23").Value = Range("B23").Value - 1
                Range("B25").Value = Range("B25").Value + 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$69:$D$74"
         End Select
    
     Case "$O$6"
         Case "Y"
            Range("B19").Value = Range("B19").Value - 1
            Range("B23").Value = Range("B23").Value + 1
         Case "A"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
         Case "M"
            Range("B15").Value = Range("B15").Value - 1
            Range("B19").Value = Range("B19").Value + 1
            Range("B21").Value = Range("B21").Value + 1
            Range("B23").Value = Range("B23").Value - 1
         Case "O"
            Range("B15").Value = Range("B15").Value - 2
            Range("B17").Value = Range("B17").Value - 2
            Range("B19").Value = Range("B19").Value + 1
            Range("B23").Value = Range("B23").Value - 1
         Case "V"
            Range("B15").Value = Range("B15").Value - 1
            Range("B17").Value = Range("B17").Value - 1
            Range("B19").Value = Range("B19").Value + 1
            Range("B21").Value = Range("B21").Value + 1
            Range("B23").Value = Range("B23").Value - 1
            Range("B25").Value = Range("B25").Value - 1
            
              
End Select

End Sub

jproffer · Jun 16, 2018

If the target is O6, it doesn't know what the cases below that are for. Add this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$H$2"
        Select Case Target.Value
            Case "Hu"
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$A$231:$A$232"
             Case "Ce"
                Range("B15").Value = Range("B15").Value + 2
                Range("B23").Value = Range("B23").Value + 2
                Range("B25").Value = Range("B25").Value - 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$57:$D$62"
              Case "Dw"
                Range("B23").Value = Range("B23").Value + 1
                Range("B25").Value = Range("B25").Value - 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$46:$D$53"
              Case "El"
                Range("B17").Value = Range("B17").Value + 1
                Range("B23").Value = Range("B23").Value - 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$12:$D$30"
              Case "Gl"
                Range("B15").Value = Range("B15").Value + 1
                Range("B23").Value = Range("B23").Value + 1
                Range("B25").Value = Range("B25").Value - 3
              Case "Gn"
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$31:$D$40"
              Case "Go"
                Range("B17").Value = Range("B17").Value + 2
                Range("B25").Value = Range("B25").Value - 2
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$54:$D$56"
              Case "HG"
                Range("B17").Value = Range("B17").Value + 1
                Range("B25").Value = Range("B25").Value - 1
              Case "HC"
                Range("B15").Value = Range("B15").Value + 2
                Range("B23").Value = Range("B23").Value + 2
                Range("B25").Value = Range("B25").Value - 3
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$A$231:$A$232"
              Case "Hl"
                Range("B15").Value = Range("B15").Value - 1
                Range("B17").Value = Range("B17").Value + 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$41:$D$45"
              Case "HO"
                Range("B15").Value = Range("B15").Value + 1
                Range("B23").Value = Range("B23").Value + 1
                Range("B25").Value = Range("B25").Value - 2
              Case "LM"
                Range("B15").Value = Range("B15").Value + 1
                Range("B17").Value = Range("B17").Value + 1
                Range("B25").Value = Range("B25").Value - 3
              Case "Mi"
                Range("B15").Value = Range("B15").Value + 2
                Range("B23").Value = Range("B23").Value + 1
                Range("B23").Value = Range("B23").Value - 3
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$63:$D$68"
              Case "WF"
                Range("B21").Value = Range("B21").Value + 1
                Range("B17").Value = Range("B17").Value + 1
                Range("B23").Value = Range("B23").Value - 1
                Range("B25").Value = Range("B25").Value + 1
                Range("K2").Validation.Delete
                Range("K2").Validation.Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                    Formula1:="=Race!$D$69:$D$74"
         End Select
    
     Case "$O$6"
        [COLOR=#ff0000][B]Select Case Target.Value[/B][/COLOR]
         Case "Y"
            Range("B19").Value = Range("B19").Value - 1
            Range("B23").Value = Range("B23").Value + 1
         Case "A"
            Range("B15").Value = Range("B15").Value + 1
            Range("B19").Value = Range("B19").Value + 1
         Case "M"
            Range("B15").Value = Range("B15").Value - 1
            Range("B19").Value = Range("B19").Value + 1
            Range("B21").Value = Range("B21").Value + 1
            Range("B23").Value = Range("B23").Value - 1
         Case "O"
            Range("B15").Value = Range("B15").Value - 2
            Range("B17").Value = Range("B17").Value - 2
            Range("B19").Value = Range("B19").Value + 1
            Range("B23").Value = Range("B23").Value - 1
         Case "V"
            Range("B15").Value = Range("B15").Value - 1
            Range("B17").Value = Range("B17").Value - 1
            Range("B19").Value = Range("B19").Value + 1
            Range("B21").Value = Range("B21").Value + 1
            Range("B23").Value = Range("B23").Value - 1
            Range("B25").Value = Range("B25").Value - 1
        [COLOR=#ff0000][B]End Select [/B][/COLOR]   
              
End Select

End Sub

Highlighted in red.

I should have noticed that when you posted just the bottom half, but it didn't occur to me until I saw the difference between that and the top "target area"

ternelson · Jun 16, 2018

That did the trick ... thanks again .... looks like im not the only one that gets up early ....

jproffer · Jun 16, 2018

Every day. Can't help it. I've been on day shift for 15+ years lol

ternelson · Jun 16, 2018

jproffer said:
Every day. Can't help it. I've been on day shift for 15+ years lol

I know the feeling .... im up early every morning .... next issue for me if you dont mind ... if B15=18 and c15<50 + 1 to E15 - than if B15=18 and c15<75 but >50 + 2 to E15.. i tried to add to formula but it gave no output..

this is what i had ..
=IF(C15<50,3,IF(AND(C15>50,C15<75),3,IF(AND(C15>75,C15<90),4,IF(AND(C15>90,C15<99),5,IF(C15=100,6,0)))))))

then i tried

=IF(B15=16,1,IF(B15=17,1,IF(B15=18,2,IF(AND(B15=18,C15<50,3,
and had no luck either...

jproffer · Jun 16, 2018

This makes 50 and less 1, then every 25 beyond that adds 1...which I'm not entirely sure is what you want, but that's what I sorta got out of your formulas and description...so if I'm way off, we'll try again

.

Book1

B

C

D

E

F

1

18

1

2

18

49

1

3

18

50

1

4

18

51

2

5

18

74

2

6

18

75

2

7

18

76

3

8

18

99

3

9

18

100

3

10

18

101

4

11

18

124

4

12

18

125

4

13

18

126

5

Sheet1

Cell Formulas
Range	Formula
F1	=IF(AND(B1=18,C1>50),ROUNDUP((C1/25),0)-1,1)

New to VB for excel..

ternelson

New Member

ternelson

New Member

Excel Facts

jproffer

Well-known Member

ternelson

New Member

jproffer

Well-known Member

ternelson

New Member

jproffer

Well-known Member

ternelson

New Member

jproffer

Well-known Member

ternelson

New Member

jproffer

Well-known Member

Similar threads

Forum statistics

Share this page

New to VB for excel..

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock