matching values and inputing value in the adjacent cell

bundyloco · #1 07-17-2005, 02:50 AM

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

#2 07-17-2005, 04:05 AM

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" <bundyloco.1say2a_1121583916.1391@excelforum-nospam.com> wrote
in message news:bundyloco.1say2a_1121583916.1391@excelforum-nospam.com...
>
> 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
>

Welcome to the Excel Forum

Forum Statistics