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

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.


On Apr 13, 9:36 am, Bill <B...@discussions.microsoft.com> wrote:
> 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

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)



---
Regards,
Norman


"Bill" <(E-Mail Removed)> wrote in message
news:5EC1287F-A926-4503-8F6F-(E-Mail Removed)...
>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

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

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

"Michael Malinsky" wrote:

> 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.
>
>
> On Apr 13, 9:36 am, Bill <B...@discussions.microsoft.com> wrote:
> > 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
>
>
>

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

"Michael Malinsky" wrote:

> 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.
>
>
> On Apr 13, 9:36 am, Bill <B...@discussions.microsoft.com> wrote:
> > 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
>
>
>

I figured it out.

Thanks again
Bill