Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching cell values | Excel Worksheet Functions | |||
Matching Criteria in adjacent columns | Links and Linking in Excel | |||
Calculations based on adjacent cell values | Excel Discussion (Misc queries) | |||
Matching Adjacent Columns? | Excel Worksheet Functions | |||
referencing values in adjacent cells to selected cell | Excel Programming |