Excel vb macro....

[Hive]

Hi there,

im creating a macro to change several things in one go in excel, itl have to be done a few hundred time, so a macro is the way to go.

My prob is im a n00b at this (got taught for a year how to program vb, 2 years ago) and was wondering if anyone can help me with something.

Im trying to take a name out of a short list, cut and paste jobby, prob is theres 2 of these in each cell (i.e First Name xxxx Last Name xxxx) id like to cut "Last Name xxxx" and paste it to a different cell, but am not sure how to do so, i think its summit like asc(mid asi remember using that for something similar, but as i say, im crap at this :-) telling vb when to start and then STOP copying i dont know how to do.

Cheers in advance

[vass0922]

ok So you have "First Name xxxx Last Name xxxx" in each cell and you want to strip out the xxxx xxxx from first name and last name?

Hmm there anyway you can clean up first name and last name? lol (get rid of the space, just cleaner for a data header)
No worries its all the same

Ok .. the way I would do it..

You can use the InStr function to get the position of a specific text in a given string
DOH.. just realized.. Stupid VBA.. sorry.. VBA uses the FIND function inconsistencies...
The Mid function to actually pull the text from the string.

Dim iLastNamePos As Integer
Dim sFirstName As String
Dim sLastName As String

iLastNamePos = Find("Last Name", CellIndex )

' params
' First = string where our text resides, 2nd where we start, third the number of characters to strip
' here I say the length of the string minus 1 because there is a space between "Last Name" and the first name in the previous part of the string

sFirstName = Mid(CellIndex, 1, iLastNamePos -1)
sLastName = Mid(CellIndex, iLastNamePos, Len(CellIndex) - iLastNamePos)

' Mid(cell, where last name starts, then to the end of the string (len) MINUS the first name stuff, so we use the position of last name)
' Or is this completely off base from what you need?

[Hive]

I think thats it cheers, aint got time to check it out cuss i gotta go to work. cheers for the help, ill be back here if i need any more :-D

[Hive]

Right, im taking "Last Name xxxxx" out and putting it in another cell, to put it in the other cell i do summit like "Dim sLastNameCell = String" and say sLastNameCell = (sLastname, cellindex)" ???

doesnt look rite to me, but i cant really do much, need a book on this i think :-)

[Hive]

er :-)

Is "Cellindex" something like A5, X=1,Y=7 or something else i havnt htought of?

[vass0922]

yeah cellindex I can't remember the exact code for it lol sorry
I'm not real strong on excel macros.
You won't be able to use A5 it'll be something like cell.value(a5) or something along those lines

[Hive]

I messed about with it for an hour, and came across this thing which MIGHT be able to help,


Range("A5").Select
Application.CutCopyMode = True
ActiveCell.FormulaR1C1 = "Last Name Smith"

Only prob is getting it to grab the name as well, as the name changes for each person, something like was said above, that would let me tell it to select Last Name and then the name, however long it is.

Also, i noticed the First name answer is separated from "Last Name" by 3 spaces, could this be used to show where to start the cut from? Something like When ASCII = 3*160 Then CutCopymode = true, thats obviously not it, but something to that effect?

Does my n00bness show thru too much? :-)

Thanks again for the help so far

[Hive]

it looks like im spamming, but its not intentional, im just kinda excited at doin all this stuff again :-)

ive written some code that i havnt tested (i know it wont work) but id like you to have a look at it anyway, just to tell me what u think :-)

Range("A5").Select
Application.CutCopyMode = xlCut
ActiveCell.FormulaR1C1 = "First Name"
Range("A5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Last Name"
Call Finder


End Sub


Sub Finder(ByRef valid As Boolean)
valid = False
firstspace = False
secondspace = False


ix = 1
Do While valid = False
If (Asc(Mid(Cells(5, 3), ix, 1)) = 32) Then
firstspace = True
Else
ix = ix + 1
Loop
End If

If firstspace = True Then
ix = ix + 1
If (Asc(Mid(Cells(5, 3), ix, 1)) = 32) Then
secondspace = True
Else
ix = ix + 1
End If
Loop
End If
If secondspace = True Then
ix = ix + 1
If (Asc(Mid(Cells(5, 3), ix, 1)) = 32) Then
valid = True
Else
ix = ix + 1
Loop
If valid = True Then
Application.CutCopyMode = xlCut
etc :-)

End Sub


Thanks for your patience

[vass0922]

did you have any luck with this?
sorry forgot to look at it earlier, and too tired now lol

btw if you use the [code] tag it keeps the indenting so its not as ugly