How to search a few latters in paragraph?

[geniusideas]

I wanted to search whole sheet for letter example "http:" in sheet 1
then after that collect the data next after http: and put it into
sheet 2. Anybody pls help how to do in VBA code?

http://e-forextrading.blogspot.com

 

[Guest]

Sub find_um()
Dim s As String
s = "http:"
Sheets("Sheet1").Activate
Set rr = ActiveSheet.UsedRange
For Each r In rr
a = r.Address
v = r.Value
If InStr(v, s) <> 0 Then
Sheets("Sheet2").Range(a).Value = Replace(v, s, "")
End If
Next
End Sub

 

[geniusideas]

Sub find_um()
Dim s As String
s = "http:"
Sheets("Sheet1").Activate
Set rr = ActiveSheet.UsedRange
For Each r In rr
a = r.Address
v = r.Value
If InStr(v, s) <> 0 Then
Sheets("Sheet2").Range(a).Value = Replace(v, s, "")
End If
Next
End Sub

Dear Gary,

It work but I need to collect only latter after the http: not before
example:
before:
aaaaa http://www.geniusideas.biz ggggg
after :
http://www.geniusideas.biz
anyway.Tq very much

 

[Ron Rosenfeld]

I wanted to search whole sheet for letter example "http:" in sheet 1
then after that collect the data next after http: and put it into
sheet 2. Anybody pls help how to do in VBA code?

http://e-forextrading.blogspot.com

Assuming that your "data" ends at either the next <space> or the end of the
string, the following might do what you describe:

======================================
Option Explicit

Sub GetURL()
Dim lStart As LoadPictureConstants, lEnd As Long
Dim c As Range
Dim i As Long
Dim URL As String
Dim sDest As Worksheet

Set sDest = Worksheets("sheet2")
i = 1

For Each c In ActiveSheet.UsedRange
lStart = InStr(c.Text, "http:")
If lStart > 0 Then
lStart = lStart + 5 'start AFTER the http:
lEnd = InStr(lStart, c.Text, " ")
If lEnd = 0 Then lEnd = Len(c.Text) + 1
URL = Mid(c.Text, lStart, lEnd - lStart)
sDest.Cells(i, 1).Value = URL
i = i + 1
End If
Next c

End Sub
================================
--ron

 

[Ron Rosenfeld]

Assuming that your "data" ends at either the next <space> or the end of the
string, the following might do what you describe:

======================================
Option Explicit

Sub GetURL()
Dim lStart As LoadPictureConstants, lEnd As Long
Dim c As Range
Dim i As Long
Dim URL As String
Dim sDest As Worksheet

Set sDest = Worksheets("sheet2")
i = 1

For Each c In ActiveSheet.UsedRange
lStart = InStr(c.Text, "http:")
If lStart > 0 Then
lStart = lStart + 5 'start AFTER the http:
lEnd = InStr(lStart, c.Text, " ")
If lEnd = 0 Then lEnd = Len(c.Text) + 1
URL = Mid(c.Text, lStart, lEnd - lStart)
sDest.Cells(i, 1).Value = URL
i = i + 1
End If
Next c

End Sub
================================
--ron

OOPS!! Should be:

=======================================
Sub GetURL()
Dim lStart As Long, lEnd As Long
Dim c As Range
Dim i As Long
Dim URL As String
Dim sDest As Worksheet

Set sDest = Worksheets("sheet2")
i = 1

For Each c In ActiveSheet.UsedRange
lStart = InStr(c.Text, "http:")
If lStart > 0 Then
lStart = lStart + 5 'start AFTER the http:
lEnd = InStr(lStart, c.Text, " ")
If lEnd = 0 Then lEnd = Len(c.Text) + 1
URL = Mid(c.Text, lStart, lEnd - lStart)
sDest.Cells(i, 1).Value = URL
i = i + 1
End If
Next c

End Sub
=================================

--ron

 

[Guest]

Sub find_um_a()
Dim s As String
s = "http:"
t = Chr(10)
Sheets("Sheet1").Activate
Set rr = ActiveSheet.UsedRange
For Each r In rr
a = r.Address
v = r.Value
If InStr(v, s) <> 0 Then
q = Split(Replace(v, s, t), t)(1)
Sheets("Sheet2").Range(a).Value = q
End If
Next
End Sub

This version ignores the text before http: