Check a value in a range

[Bill]

I am trying to check a cell to see if it's a C. The example below is a sub
that is called from a Do Loop. The line checkcellA = Worksheets .... is
causing problems. Is it because of the variable counter??



Sub sub1()
Dim checkcellA As Range
checkcellA = Worksheets("Inventory Sheet 1 & 2").Cells(counter, 1)
If checkcellA = "C" Then Call Sub2

End Sub

 

[Michael Malinsky]

The variable "counter" is the problem because the code will not pass
the value of the variable from one sub to another automatically. You
have to tell it to do that. So when you call sub1 from your main sub
you need to use

sub1(counter)

provided that counter is the variable name in your main sub. You then
need to change sub1 to read:

Sub sub1(counter as Integer)

I believe that should help. If not post back.

 

[Norman Jones]

Hi Bill,

In addition to Michael's suggestion, a range object, or, indeed,
any object, must be instantiated usinf the Set statement, e.g.:

change

checkcellA = Worksheets("Inventory Sheet 1 & 2").Cells(counter, 1)

to

set checkcellA = Worksheets("Inventory Sheet 1 & 2").Cells(counter, 1)

 

[Bill]

I got past that error - thanks to both of the replies, but am still having
problems. The whole thing is pretty simple, I'm just new to this and don't
really know the language/methods. Here is what I have so far. Basically I
start at b12 and check for a value then go to a12, where 12 is the counter
value. Then check that cell for a "C" - if it's there i want
cell(counter,13) to get what's in cell u24. In cell(counter,14) i want a
value of the jobnumcount (which is initially the value of cell v24+1). Each
time it finds a "C" the value of jobnumcount needs to increase by one (a new
job number). It should keep going till column b has nothing in it.

see below for current program

Thanks a lot for any help.

Private Sub CommandButton2_Click()
Dim counter As Integer
Dim jobnumcount, var1, var2
jobnumcount = v24 + 1
counter = 12
curcell = Worksheets("Inventory Sheet 1 & 2").Cells(counter, 2)
Do Until curcell = 0
Call sub1(counter)
counter = counter + 1
curcell = Worksheets("Inventory Sheet 1 & 2").Cells(counter, 2)
Loop
Worksheets("Inventory Sheet 1 & 2").Range("B12").Select
End Sub


'Check If Consumable
Sub sub1(counter As Integer)
Dim checkcellA As Range
Set checkcellA = Worksheets("Inventory Sheet 1 & 2").Cells(counter, 1)
If checkcellA = "C" Then Call Sub2(counter)
End Sub

'Insert New Job Number

Sub Sub2(counter As Interior)
Worksheets("Inventory Sheet 1 & 2").Cells(counter, 13) = Range("u24")
Worksheets("Inventory Sheet 1 & 2").Cells(counter, 14) = jobnumcount
jobnumcount = jobnumcount + 1
End Sub

 

[Bill]

Also-how do you check the values of the variables such as counter? How can I
run the program line by line and watch the progress?

Thanks again

 

[Bill]

I have it almost working. Below is the last sub that I use. I have two
variables that I need to come from the main program to this sub. One is
integer (which works fine) and the other is jobnumcount. How do I get
jobnumcount available to this sub?
thanks
Bill


Sub Sub2(counter As Integer)
Worksheets("Inventory Sheet 1 & 2").Cells(counter, 13) = Range("u24")
Worksheets("Inventory Sheet 1 & 2").Cells(counter, 14) = jobnumcount
jobnumcount = jobnumcount + 1
End Sub

 

[Bill]

I figured it out.

Thanks again
Bill