matching values and inputing value in the adjacent cell
 

Hi,

I posted a question about this last week, but had to remove it becaus
I accidently included some information that I did not want out on th
web. Here is my code.

I am trying to loop through a colum and look for certain criteria. I
the cell meets the criteria, it inserts a value to the adjent cell i
the next column. However, not all the cells in column a have are fille
in fact, a lot of them are empty, but I want the program to loop throug
until the end of the column. Can I just make this an OR statement tha
also include a column that always has data so when both are empt
together, I know it is at the end of the range?

When I run the code, it gives me an error for the "i" in the Next
statement. It also gives me a type mismatch error in the if rows. I ca
get this error to stop if I get rid of the OR statement so I'm thinkin
that is the problem even though it doesn't make sense to me.


Dim a As String
Dim cntr As Integer

'loop through cells to find product and input the corresonding rout
one
'cell to the right

For cntr = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If a = "Product A-1" Or "Product A-2" Then
ActiveCell.Offset(0, 1).Value = "123.6ABC"
ElseIf a = " Product B-0" Or "Product B-1" Then
ActiveCell.Offset(0, 1).Value = "456.6CDE"
ElseIf a = "Product C-1" Then
ActiveCell.Offset(0, 1).Value = "789.1A2BC"
Else
cntr = cntr + 1
End If
Next

--
bundyloc
-----------------------------------------------------------------------
bundyloco's Profile: http://www.excelforum.com/member.php...fo&userid=2338
View this thread: http://www.excelforum.com/showthread.php?threadid=38781

matching values and inputing value in the adjacent cell
 

Hi, you will need to modify your code.
1. The For loop needs to be closed with a Next cntr or Next but NOT Next i
2. For multiple conditions you must specify the value for each so
a = "string1" Or "string2" should read a= "string1" Or a = "string2"
3. You have not declared what 'a' is so use a=cells(cntr,1)
4. The use of activecell is not valid as you are not selecting cells as the
loop progresses (normally a good approach) however you will need to specify
the location of the result so Cells(cntr,2) relates to the test row, column
2.
5. Finally you might consider using Select Case for your conditions as lots
of nested If else can be confusing

The modified code without the last point (5) incorporated is listed below.


Dim a As String
Dim cntr As Integer

For cntr = 1 To Cells(Rows.Count, "A").End(xlUp).Row

a = Cells(cntr, 1).Value

If a = "Product A-1" Or a = "Product A-2" Then
Cells(cntr, 2).Value = "123.6ABC"
ElseIf a = " Product B-0" Or a = "Product B-1" Then
Cells(cntr, 2).Value = "456.6CDE"
ElseIf a = "Product C-1" Then
Cells(cntr, 2).Value = "789.1A2BC"
End If

Next cntr


--
Cheers
Nigel



"bundyloco" wrote
in message ...

Hi,

I posted a question about this last week, but had to remove it because
I accidently included some information that I did not want out on the
web. Here is my code.

I am trying to loop through a colum and look for certain criteria. If
the cell meets the criteria, it inserts a value to the adjent cell in
the next column. However, not all the cells in column a have are filled
in fact, a lot of them are empty, but I want the program to loop through
until the end of the column. Can I just make this an OR statement that
also include a column that always has data so when both are empty
together, I know it is at the end of the range?

When I run the code, it gives me an error for the "i" in the Next i
statement. It also gives me a type mismatch error in the if rows. I can
get this error to stop if I get rid of the OR statement so I'm thinking
that is the problem even though it doesn't make sense to me.


Dim a As String
Dim cntr As Integer

'loop through cells to find product and input the corresonding route
one
'cell to the right

For cntr = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If a = "Product A-1" Or "Product A-2" Then
ActiveCell.Offset(0, 1).Value = "123.6ABC"
ElseIf a = " Product B-0" Or "Product B-1" Then
ActiveCell.Offset(0, 1).Value = "456.6CDE"
ElseIf a = "Product C-1" Then
ActiveCell.Offset(0, 1).Value = "789.1A2BC"
Else
cntr = cntr + 1
End If
Next i


--
bundyloco
------------------------------------------------------------------------
bundyloco's Profile:
http://www.excelforum.com/member.php...o&userid=23386
View this thread: http://www.excelforum.com/showthread...hreadid=387816