I'm new and need some help with some cell editing in vba

[vbakid]

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!

 

[Weaver]

A useful command in VB is split() which can turn a string into an array based on a defined delimiter.

Son myArray=split(range("A1").value,",")

will break the string based on where the commas are

So then you can use ubound() to determine the number of elements (remember that by default the first one is at zero) enabling you to determine the last and indeed the second to last value.

HTH

 

[VoG]

For the first one, try like this

Sub NoVersion()
Dim LR As Long, i As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value, " ")
        If X(UBound(X)) Like "*version*" Then .Value = X(LBound(X))
    End With
Next i
End Sub

 

[vbakid]

A useful command in VB is split() which can turn a string into an array based on a defined delimiter.

Son myArray=split(range("A1").value,",")

will break the string based on where the commas are

So then you can use ubound() to determine the number of elements (remember that by default the first one is at zero) enabling you to determine the last and indeed the second to last value.

HTH

Really having trouble understanding this, I typed in ubound on the Microsoft visual basic help on excel and i'm still very lost

 

[VoG]

From the Help

UBound Function


Returns a Long containing the largest available subscript for the indicated dimension of an array.
Syntax
UBound(arrayname[, dimension])
The UBound function syntax has these parts:
<table cellpadding="4" cellspacing="4" cols="2"> <tbody> <tr valign="top"> <th width="17%">Part</th> <th width="83%">Description</th></tr> <tr valign="top"> <td class="T" width="17%">arrayname</td> <td class="T" width="83%">Required. Name of the array variable; follows standard variable naming conventions.</td></tr> <tr valign="top"> <td class="T" width="17%">dimension</td> <td class="T" width="83%">Optional; Variant (Long). Whole number indicating which dimension's upper bound is returned. Use 1 for the first dimension, 2 for the second, and so on. If dimension is omitted, 1 is assumed.</td></tr></tbody></table>
Remarks
The UBound function is used with the LBound function to determine the size of an array. Use the LBound function to find the lower limit of an array dimension.
UBound returns the following values for an array with these dimensions:

<code>Dim A(1 To 100, 0 To 3, -3 To 4) </code> <table cellpadding="4" cellspacing="4" cols="2"> <tbody> <tr valign="top"> <th width="27%">Statement</th> <th width="73%">Return Value</th></tr> <tr valign="top"> <td class="T" width="27%"><code>UBound(A, 1)</code></td> <td class="T" width="73%">100</td></tr> <tr valign="top"> <td class="T" width="27%"><code>UBound(A, 2)</code></td> <td class="T" width="73%">3</td></tr> <tr valign="top"> <td class="T" width="27%"><code>UBound(A, 3)</code></td> <td class="T" width="73%">4</td></tr></tbody></table>

 

[Weaver]

it's ubound()

myArray=split("A,B,C,D",",")

would result in the array {"A","B","C","D"}

x = ubound(myArray) would yield the result of x=3

so myArray(x) gives you your last value

myArray(x-1) gives the second to last value.

 

[vbakid]

it's ubound()

myarray=split("a,b,c,d",",")

would result in the array {"a","b","c","d"}

x = ubound(myarray) would yield the result of x=3

so myarray(x) gives you your last value

myarray(x-1) gives the second to last value.

awsome!!!!!!