VBA to select the cells next to the result of a find & combine the
 

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!

VBA to select the cells next to the result of a find & combine the
 

Try this:

Sub fndCase()
Dim c As Range
With Worksheets(1).UsedRange 'Change to actual
Set c = .Find("Case", After:=Range("A65535"), LookIn:=xlValues)
If Not c Is Nothing Then
fRng = c.Address
Range("G4") = Range(fRng).Offset(0, 1).Value & _
Range(fRng).Offset(0, 2).Value
End If
End With
End Sub

"ArielZusya" wrote:

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!

VBA to select the cells next to the result of a find & combine
 

Forgot I was in a With statement. Put the dot before the Range ref.
so it won't jump to the active sheet and error out.

Sub fndCase()
Dim c As Range
With Worksheets(1).UsedRange 'Change to actual
Set c = .Find("Case", After:=Range("A65535"), LookIn:=xlValues)
If Not c Is Nothing Then
fRng = c.Address
.Range("G4") = .Range(fRng).Offset(0, 1).Value & _
..Range(fRng).Offset(0, 2).Value
End If
End With
End Sub



"JLGWhiz" wrote:

Try this:

Sub fndCase()
Dim c As Range
With Worksheets(1).UsedRange 'Change to actual
Set c = .Find("Case", After:=Range("A65535"), LookIn:=xlValues)
If Not c Is Nothing Then
fRng = c.Address
Range("G4") = Range(fRng).Offset(0, 1).Value & _
Range(fRng).Offset(0, 2).Value
End If
End With
End Sub

"ArielZusya" wrote:

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!

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!

VBA to select the cells next to the result of a find & combine
 

Thanks for your quick reply. I'm not following your suggestion. I'm sure
I'm missing a step or something. I copied your code and put it in the spot
I'd need to do the find, select, combine, and paste but it didn't appear to
do anything. Is there more I'm supposed to do? I'm still lost. Thanks for
your continued help.

"JLGWhiz" wrote:

Try this:

Sub fndCase()
Dim c As Range
With Worksheets(1).UsedRange 'Change to actual
Set c = .Find("Case", After:=Range("A65535"), LookIn:=xlValues)
If Not c Is Nothing Then
fRng = c.Address
Range("G4") = Range(fRng).Offset(0, 1).Value & _
Range(fRng).Offset(0, 2).Value
End If
End With
End Sub

"ArielZusya" wrote:

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!