I am seaching through a column of data and when I find a cell that has
matching data I want to insert data into other columns in the same
row.

Also How do I define a blank cell in my search? I don't want any
insertions past the last row of data as I'm thinking if I have the
"blank" cell case it will keep on inserting data into the other columns
for ever.


Code:
--------------------
Columns("B:B").Select

For Each C In Selection
Select Case UCase(C)
Case "115297": then insert into A "22" and insert into I "23"
Case "276534": then insert into A "27" and insert into I "93"
Case "blank?": then insert into A "999" and insert into I "999"

End Select
Next C
--------------------



---
Message posted from http://www.ExcelForum.com/

there are a few diferent ways to code for this - this is just one o
them

For Each C In Selection
Select Case UCase(C)
Case "115297" ': then insert into A "22" and insert into I "23"
range("a" & cell.row).value = "22"
range("i" & cell.row).value = "23"

Case "276534" ' : then insert into A "27" and insert into I "93"
range("a" & cell.row).value = "27"
range("i" & cell.row).value = "93"

Case IsEmpty 'then insert into A "999" and insert into I "999"

case isEmpty
range("a" & cell.row).value = "999"
range("i" & cell.row).value = "999"
exit for
End Select
Next

--
Message posted from http://www.ExcelForum.com

Joh

one way

Ton

Sub eee(
Range("b1").Selec
While Not IsEmpty(ActiveCell
Select Case ActiveCell.Valu
Case 11529
ActiveCell.Offset(0, -1).Value = 2
ActiveCell.Offset(0, 7).Value = 2

Case 27654
ActiveCell.Offset(0, -1).Value = 2
ActiveCell.Offset(0, 7).Value = 9
End Selec
ActiveCell.Offset(1, 0).Selec
Wen
End Sub

Straight up I got an error with *Case IsEmpty*

so I commented it out and then I get an "object required" error

when I get to the first case

Case "115297"
Range("A" & Cell.Row).Value = "1" *<-- error here*

Maybe I did something wrong

--
Message posted from http://www.ExcelForum.com

~× wrote:
*John

one way.

Tony

Sub eee()
Range("b1").Select
While Not IsEmpty(ActiveCell)
Select Case ActiveCell.Value
Case 115297
ActiveCell.Offset(0, -1).Value = 22
ActiveCell.Offset(0, 7).Value = 23

Case 276543
ActiveCell.Offset(0, -1).Value = 27
ActiveCell.Offset(0, 7).Value = 93
End Select
ActiveCell.Offset(1, 0).Select
Wend
End Sub *

I have empty cells in the column. Won't this stop when it reaches the
first empty cell?

I assume the offset this is counting columns from the cell being
searched?


---
Message posted from http://www.ExcelForum.com/

Sorry my mistake

I was working on several different problems at the same time

Sub cfffg()
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "115297" ': then insert into A "22" and insert into I "23"
Range("a" & cell.Row).Value = "22"
Range("i" & cell.Row).Value = "23"

Case "276534" ' : then insert into A "27" and insert into I "93"
Range("a" & cell.Row).Value = "27"
Range("i" & cell.Row).Value = "93"

'Case "" 'then insert into A "999" and insert into I "999"

Case ""
Range("a" & c.Row).Value = "999"
Range("i" & c.Row).Value = "999"
Exit For
End Select
Next
End Sub


---
Message posted from http://www.ExcelForum.com/

mudraker wrote:
*Sorry my mistake

I was working on several different problems at the same time

Sub cfffg()
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "115297" ': then insert into A "22" and insert into I "23"
Range("a" & cell.Row).Value = "22"
Range("i" & cell.Row).Value = "23"

Case "276534" ' : then insert into A "27" and insert into I "93"
Range("a" & cell.Row).Value = "27"
Range("i" & cell.Row).Value = "93"

'Case "" 'then insert into A "999" and insert into I "999"

Case ""
Range("a" & c.Row).Value = "999"
Range("i" & c.Row).Value = "999"
Exit For
End Select
Next
End Sub *

Thanks mudraker but I still seem to be getting *Run-time error '424"
object required* when it hits *Range("a" & cell.Row).Value = "22"*


---
Message posted from http://www.ExcelForum.com/

John

I failed to fix all the errors in my first posting
I should have changed all Cell.row entries to be c.row

I fixed in the last part of the macro & not in the first part in m
reposted macro



Sub cfffg()
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "115297" ': then insert into A "22" and insert into I "23"
Range("a" & c.Row).Value = "22"
Range("i" & c.Row).Value = "23"

Case "276534" ' : then insert into A "27" and insert into I "93"
Range("a" & c.Row).Value = "27"
Range("i" & c.Row).Value = "93"

'Case "" 'then insert into A "999" and insert into I "999"

Case ""
Range("a" & c.Row).Value = "999"
Range("i" & c.Row).Value = "999"
Exit For
End Select
Next
End Su

--
Message posted from http://www.ExcelForum.com