Character Analysis

[Derek Johansen]

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!

 

[Jacob Skaria]

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

 

[Derek Johansen]

Umm... all this is doing is giving me an output of cell A1... That's not what
I'm looking for. What I need is to count the number of spaces in the cell
from the begining of the cell, and then when the 3rd space occurs, i will be
copying all the text before it. For example:

If I cell contains: "2 X 4 Lumber"

I want to identify the third space after the 4, and then copy all the text
from ahead of that space 2 X 4 and paste into a different location. COPY,
not cut, but I can deal with those types of semantics.

 

[Jacob Skaria]

With "2 X 4 Lumber" in A1

Try the below macro.. which will copy 2 x 4 to Range("B1")


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

If this post helps click Yes

 

[Derek Johansen]

This copied "2 X" and no last character.

This macro needs to also work for "2 X 6 " "2 X 8 ", "2 X 10 ", "2 X 12 "
This is why i simply want to search each cell for the 3rd space and copy
before. I don't want it based on the numbers at all...

 

[Jacob Skaria]

Derek

You must be having more spaces in betwen X and 4. The below is not based on
any particular number.. Try with the below values in cell A1. It will extract
the 1st three

a b c d e f
1 2 3 4 5

Any way I have modified the macro to handle multiple spaces. Try and feedback

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

 

[Derek Johansen]

Thank you! I think what fixed it was that you trimmed A1 before we evaluated
it. The problem before was that when I imported my data the other software
program had put a space BEFORE the 2. So it was picking up the third space
to be after the X instead of after the last number. I really appreciate the
help, and was wondering if you could give me assistance on one more thing:

Ultimately what I had planned to do with this was to eliminate the " X " and
just have "24" or "210" as an output (more will be added to this string, but
all i need from the current cell is that). Would the way to do this be to
use a split function with " X " as a deliminator? I think I could figure
that out if that is the most effecient way!

Again, thanks for your help, the macro works as desired

 

[Jacob Skaria]

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

 

[Derek Johansen]

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!

 

[Derek Johansen]

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]

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

 

[Derek Johansen]

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