View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default VBA to select the cells next to the result of a find & combine the

Code something like this should do what you want...

Dim R As Range
Dim Cel As Range
Dim LookUpRow As String
Dim LastUsedRow As Long
LookUpRow = "C"
LastUsedRow = Cells(Rows.Count, LookUpRow).End(xlUp).Row
For Each Cel In Range(LookUpRow & "1:" & LookUpRow & LastUsedRow)
If Cel.Value = "Case:" Then
Range("G4").Value = Cel.Offset(0, 1).Value & _
Cel.Offset(0, 2).Value
Exit For
End If
Next

Rick


"ArielZusya" wrote in message
...
I've got data that always appears in the two cell immediately to the right
of
a cell that contains "Case:" that I'd like to select. In other words if
"Case:" appears in C33, then the two cells I want to select are in D33 and
E33. The trouble is the row isn't always the same from time to time (the
sheet is as a result of imported text). I can do a find to locate the
cell
with "Case:" but then what do I do? This seems like it should be so
simple
but I'm coming up blank.

Once I select the contents of those two cells, I'd like to combine the
contents of the two cells into a third cell. In other words if the
contents
of the cell in D33 is "211" and the contents of the cell in E33 is "FEG"
I'd
like to combine them into a third cell (say G4) which would have the value
of
"211FEG" therefore. Again, it doesn't seem like it should be that
difficult
but I'm struggling.

Your help would be greatly appreciated. Thanks!