Subtracting from an offset cell based on certain conditions

[Moonbeam111]

I am trying to figure this out. I have range("I22:I25"). In that range I would like to search for a particular set of words. Lets simply call them: "Example1", "Example2",and "Example3"

I am wanting to run a macro that looks for those words (example1, example2 etc....). And if it matches to subtract by 1 the number that is to the left of it. But only subtract if there is actually a number there, not if its a blank cell.

Might sound confusing but here is an example.

Macro will run. When it runs it will look for the words Example1, 2 or 3 in ColumnB. If it finds the word it then checks to the left of the cell in ColumnA and checks to see if its blank or not. If its not blank it will then subtract 1 from it.

So if I ran the macro right now. The finished result in ColumnA would be: stays empty, 2, stays empty.

ColumnA	ColumnB
	Example1
3	Example2
	Example3

 

[Peter_SSs]

Might sound confusing ..

A bit because you start of referring to column I but end up with A and B.


Give this a try with a copy of your data.

Sub Subtract_1()
  Dim a As Variant
  Dim i As Long
  
  With Range("a1", Range("B" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      Select Case a(i, 2)
        Case "Example1", "Example2", "Example3"
          If Len(a(i, 1)) > 0 And IsNumeric(a(i, 1)) Then a(i, 1) = a(i, 1) - 1
      End Select
    Next i
    .Value = a
  End With
End Sub

Before:

Moonbeam111.xlsm
	A	B
1
2		Example1
3	3	Example2
4		Example3
5	5	Example4
6	10
7	1	Example1
8	5	Example2
9		Example3
10		Example4
Sheet1

After:

Moonbeam111.xlsm
	A	B
1
2		Example1
3	2	Example2
4		Example3
5	5	Example4
6	10
7	0	Example1
8	4	Example2
9		Example3
10		Example4
Sheet1

 

[Moonbeam111]

This is quite close to what I'm trying to achieve but I couldn't modify your code to check only a range. How would I do that? So I need it to check range("I22:I25") for specific words and subtract 1 from the number (if its not blank) on range("H22:H25"). And finally, how would i modify your code to clear contents of any cell in range("H22:H25") if its a number that equals 0 or less?

 

[Peter_SSs]

The requirements are not clear. For example,
- if I22 contains one of the specific words and H22 contains 1, should the code subtract 1 from H22 leaving 0 or should it then clear H22 because it now contains "0 or less"?
- can H22:H25 ever contain text instead of numbers?

 

[Moonbeam111]

Answer to question 1 = Well both. It should subtract 1, yes, but after that it should clear the contents of that cell at the same time because it now equals 0. So end result is the cell would now be blank/empty.
Answer to question 2 = H22:H25 can only ever contain numbers. They will never be words.

 

[Peter_SSs]

Thanks for the clarifications. Try this version.

Sub Subtract_1_v2()
  Dim a As Variant
  Dim i As Long
  
  With Range("a1", Range("B" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      Select Case a(i, 2)
        Case "Example1", "Example2", "Example3"
            a(i, 1) = a(i, 1) - 1
      End Select
      If a(i, 1) <= 0 Then a(i, 1) = Empty
    Next i
    .Value = a
  End With
End Sub

Before

Moonbeam111.xlsm
	A	B
1
2		Example1
3	3	Example2
4		Example3
5	5	Example4
6	10
7	1	Example1
8	5	Example2
9	0	Example3
10	-1	Example4
Sheet1

After

Moonbeam111.xlsm
	A	B
1
2		Example1
3	2	Example2
4		Example3
5	5	Example4
6	10
7		Example1
8	4	Example2
9		Example3
10		Example4
Sheet1

 

[Moonbeam111]

Thank you that clears up the 0 cell problem. But I'm still having a hard time getting it to work with range H22:H25 and I22:25. It should look for the words in I22:I25 and the numbers are in H22:H25

Simply changing this to

With Range("H22:H25", Range("I22:I25" & Rows.Count).End(xlUp))

Doesn't work. Error: "Getting method range of object global failed".

 

[Alex Blakenburg]

Try changing it to this:

With Range("H22:I25")

 

[Peter_SSs]

But I'm still having a hard time getting it to work with range H22:H25 and I22:25.

Yes, sorry, I forgot to change from my general test range to your specific range but Alex has given you the fix for the 'With' line.

 

[Moonbeam111]

Thank you it works! I have just one last related question: I need to also have another piece of code that works exactly the same way but looks for the words in D22:D25 and subtracts the numbers from E22:E25. Could i get a little help with that?