Finding a specific text

  • Thread starter Thread starter QuietMan
  • Start date Start date
Q

QuietMan

Here is what I'm attempting to do...
Check a large range of cells for the text "#"
Once I find a cell that has the "#" text, I need to take the 4 characters
right of the "#" text and place it into a different column

Thanks
 
Simple solution to get you started; no code.
=MID(A7,FIND("#",A7)+1,4)

I'll try to come up with a solution that loops through the data later, when
I have more time.

Regards,
Ryan---
 
Thanks,

I have to do 3 checks also when looping through the data
what do you think is the best way to lop through (fastest)
 
Finally done with Fluid Dynamics so I can revisit. Try this:

Sub Convert()

Range("A1").Select
For X = 1 To 1
Dim redRng As Range

Set redRng = Range("A1", Range("A100").End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],FIND(""#"",RC[-2])+1,4)"
ActiveCell.Offset(1, -2).Select
End If

Next cell

Next X
End Sub

Regards,
Ryan---
 
Thanks for the Help...

I came up with this code

Cells(20000, 14).Select
Selection.End(xlUp).Select
EndRow = ActiveCell.Row
For X = 1 To EndRow
Eval = Left(Cells(X, 1), 10)
If Eval <> " CURRENCY" Then GoTo No_Find
Entity = Mid(Cells(X, 1), Application.Find("#", Cells(X, 1)) +
1, 4)
Do Until Cells(X, 1) = "AVAILABLE ROOMS"
X = X + 1
Loop
Range("A" & X, Range("A" & X).End(xlDown)).Offset(0, 15) = Entity
Cells(X, 1).Select
Selection.End(xlDown).Select
X = ActiveCell.Row
No_Find:
Next X

Works great but it enters a value for the entity example: Entity = "0012"
but it enters 12 in the cell any Ideas?

--
Helping Is always a good thing


ryguy7272 said:
Finally done with Fluid Dynamics so I can revisit. Try this:

Sub Convert()

Range("A1").Select
For X = 1 To 1
Dim redRng As Range

Set redRng = Range("A1", Range("A100").End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],FIND(""#"",RC[-2])+1,4)"
ActiveCell.Offset(1, -2).Select
End If

Next cell

Next X
End Sub

Regards,
Ryan---


--
RyGuy


QuietMan said:
Thanks,

I have to do 3 checks also when looping through the data
what do you think is the best way to lop through (fastest)
Click to expand...
Click to expand...
 
Back
Top