PDA

View Full Version : Solved: I'm new and need some help with some cell editing in vba (Excel)



poloalpha
09-20-2010, 12:10 AM
Hi guys,

I'm new to the world of VBA, and although im getting through OKish, im stuck on some things. The first thing is deleting a word in a cell that contains a sub-word, so for example, if we have cells like:
1000.2, old_version12_0
1800.4, new_version19_30
10050.2, new_version102_0
100.4, old_version2_0

I want something which will delete any word containing "version", so i get:
1000.2,
1800.4,
10050.2,
100.4,

My other problem is with splitting the contents of a cell and putting the two parts into variables, so if I have cells such as:
1000.2, a
1800.4, a, bc
10050.2, a, bc, a
100.4, c, aa, ab, a

I want whatever is on the RIGHT of the last comma into one variable, and whatever is on the LEFT of the last comma into another variable. i.e.
left var = 1000.2, right var = a
left var =1800.4, a, right var =bc
left var =10050.2, a, bc, right var =a
left var =100.4, c, aa, ab, right var =a

obviously though I'm aware we can't fit all these into the same variable, I just need it to work for one of the cells so I can process the information and then I will be looping through and doing the same for the next cell.

All help would be much appreciated!

Bob Phillips
09-20-2010, 12:45 AM
Dim pos As Long

With ActiveCell

pos = InStr(.Value2, "version")
If pos > 0 Then

pos = InStrRev(.Value2, " ", pos)
MsgBox Left$(.Value2, pos - 1)
End If
End With




Dim var As Variant
Dim var1 As String, var2 As String

var = Split(ActiveCell.Value2, ",")
var1 = var(LBound(var))
var2 = var(LBound(var) + 1)

GTO
09-20-2010, 02:00 AM
Greetings poloalpha,

I see you just joined, welcome to vbaexpress:thumb . I'm sure you'll be glad you did, as there's great follks here:)

As to your questions, just a different try:

As to the first bit (getting the left part of: 10050.2, new_version102_0), I'm not sure if we can count on the left part being a 'number', so this may well not be stellar. Just to see if it works, as a UDF:


Option Explicit

Function RETLEFT(ByVal CellString As String) As Variant
Static REX As Object

If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
End If

With REX
.Global = False
.IgnoreCase = True
.Pattern = "([0-9]+[.]?[0-9]*\,)(.*version.*)"
If .Test(CellString) Then
RETLEFT = .Execute(CellString)(0).SubMatches(0)
Else
RETLEFT = vbNullString
End If
End With
End Function

I was thinking that to split based on the last comma, maybe:


Function RETSPLIT(ByVal CellString As String) As Variant
Dim ary(1 To 2)
Dim Pos As Long

Pos = InStrRev(CellString, ",")
If Pos > 0 Then
ary(1) = Left(CellString, Pos)
ary(2) = Trim(Mid(CellString, Pos + 1))
Else
ary(1) = vbNullString
ary(2) = vbNullString
End If
RETSPLIT = ary
End Function

...again as a UDF, but entered as an array formula across two cells.

Hope that helps, and again, welcome to VBAX :-)

Mark