Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check a value in a range
I figured it out.
Thanks again Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check if a range contains a certain value | Excel Programming | |||
Check i a value is in a Range | Excel Programming | |||
Check if a range is a sebset of another range | Excel Programming | |||
Check if a range is a sebset of another range | Excel Programming | |||
check range for certain value | Excel Programming |