Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

You are a HUGE help! I can't believe I didn't think to use a simple replace
function! I've only been working with VB Excel for three weeks now, and the
only other experience I have is a Fortran class I took last semester in
school! People like you are really a huge help for me to learn and create
code for my summer work situation! I really really appreciate it!


-----

Is the the most efficient/

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

I have one more question for you Mr. Skaria,

I am writing what will ultimately be a pretty indepth macro, and I'd like to
utilize what I know in fortran as Functions. I don't know necessarily if
that's what they're called in VB or if it's even possible, but say for
example I wanted to use this code:
lr = Cells(Rows.Count, desc).End(xlUp).Row
With Range(Cells(2, desc), Cells(lr, desc))
Set c = .Find(What:="STD&BTR", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cells(c.Row, item_num) = Cells(c.Row, item_num)
& "2"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstaddress
End If
End With

Many times, but didn't want to have that many lines of code. I just wanted
to pass information into this little routine here for what to search for, and
then pass out a value of true or false. How would I go about doing that? If
that needs more detail, let me know, again, I REALLY appreciate all your help!

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Thanks Derek for your feedback; and welcome to this discussion group...

I have modified the code to suit your requirement. YES it is called Function
in VBA as in all other programming languages. The below function returns a
boolean value if a search is found

Function FindinRange(rngRange As Range, strFind) As Boolean
Dim rngTemp As Range
Set rngTemp = rngRange.Find(strFind, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rngTemp Is Nothing Then FindinRange = True
End Function

To use this in macro

Sub Macro1()
Dim rngMyRange As Range
Set rngMyRange = Range("A1:B100")

If FindinRange(rngMyRange, "derek") = True Then
MsgBox "Found"
'do something
End If

End Sub

OR--------------------------------------------------------------------

You can use the worksheet function itself (a little bit slower)

Sub Macro2 ()
IF Worksheetfunction.CountIf(Range("A1:B100"),"derek" )0 Then
Msgbox "Found"
'do something
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

I have one more question for you Mr. Skaria,

I am writing what will ultimately be a pretty indepth macro, and I'd like to
utilize what I know in fortran as Functions. I don't know necessarily if
that's what they're called in VB or if it's even possible, but say for
example I wanted to use this code:
lr = Cells(Rows.Count, desc).End(xlUp).Row
With Range(Cells(2, desc), Cells(lr, desc))
Set c = .Find(What:="STD&BTR", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cells(c.Row, item_num) = Cells(c.Row, item_num)
& "2"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstaddress
End If
End With

Many times, but didn't want to have that many lines of code. I just wanted
to pass information into this little routine here for what to search for, and
then pass out a value of true or false. How would I go about doing that? If
that needs more detail, let me know, again, I REALLY appreciate all your help!

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Thank you very much! I appreciate it and so you know, i plan to try to find
you for questions in the future!

Thanks,

Derek


"Jacob Skaria" wrote:

Thanks Derek for your feedback; and welcome to this discussion group...

I have modified the code to suit your requirement. YES it is called Function
in VBA as in all other programming languages. The below function returns a
boolean value if a search is found

Function FindinRange(rngRange As Range, strFind) As Boolean
Dim rngTemp As Range
Set rngTemp = rngRange.Find(strFind, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rngTemp Is Nothing Then FindinRange = True
End Function

To use this in macro

Sub Macro1()
Dim rngMyRange As Range
Set rngMyRange = Range("A1:B100")

If FindinRange(rngMyRange, "derek") = True Then
MsgBox "Found"
'do something
End If

End Sub

OR--------------------------------------------------------------------

You can use the worksheet function itself (a little bit slower)

Sub Macro2 ()
IF Worksheetfunction.CountIf(Range("A1:B100"),"derek" )0 Then
Msgbox "Found"
'do something
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

I have one more question for you Mr. Skaria,

I am writing what will ultimately be a pretty indepth macro, and I'd like to
utilize what I know in fortran as Functions. I don't know necessarily if
that's what they're called in VB or if it's even possible, but say for
example I wanted to use this code:
lr = Cells(Rows.Count, desc).End(xlUp).Row
With Range(Cells(2, desc), Cells(lr, desc))
Set c = .Find(What:="STD&BTR", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cells(c.Row, item_num) = Cells(c.Row, item_num)
& "2"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstaddress
End If
End With

Many times, but didn't want to have that many lines of code. I just wanted
to pass information into this little routine here for what to search for, and
then pass out a value of true or false. How would I go about doing that? If
that needs more detail, let me know, again, I REALLY appreciate all your help!

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!