select column cell in same row as loop result?

  • Thread starter Thread starter john_t_h
  • Start date Start date
J

john_t_h

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
--------------------
 
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
 
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
 
~× said:
*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 *
Click to expand...

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?
 
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
 
mudraker said:
*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 *
Click to expand...

Thanks mudraker but I still seem to be getting *Run-time error '424"
object required* when it hits *Range("a" & cell.Row).Value = "22"*
 
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
 
That works alot better now :-)

The only problem is when it hits a blank cell it stops. As I have
blank cells mixed in with the data, this is a bad thing.

I was thinking that perhaps the loop could use another column that
always has data in it. When that column is blank then the loop stops.
I'm not sure how it works but...

Code:
--------------------

While Column E is not empty
check c
Columns("B:B").Select
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "115297"
Range("A" & c.Row).V..............
 
Back
Top