PDA

View Full Version : Total dummy when it comes to VBA, so hoping someone can help!



Sheepy1250
06-22-2020, 02:41 AM
Hi everyone

Really not sure how to do this in VBA or with a Macro, but here goes:

I have a column of information that I currently split out using a variety of queries in a worksheet (Office 365 version)

The basic format is like this (see attached example)

+++TOWN NAME (LLLNN) - [where L=a letter and N = a number]

These sit in column A

I need to show these in a new format in two separate columns, thus:
Column 2 - TOWN NAME
Column 3 - LLLNN

Currently using the queries as follows:
=RIGHT(A1,6) - to extract the final 6 characters which brings up LLLNN) in the result
Then, in the next column:
=RIGHT(A1,LEN(A1)-3) to remove the +++ from the cell data
...and then
=LEFT(C1,LEN(C1)-7) to remove everything but the town name from the data
and finally...
=LEFT(B1,5) to show the LLLNN section I need

It works fine, but I'm hoping to create a piece of code that will perform these functions down a variable length list in column A until it meets the first blank cell.

Any suggestions very, very welcome - am trying to wrap my head around VBA but it seems to be meeting a bit of a block (my somewhat dim brain!)

Sheepy1250
06-22-2020, 02:41 AM
:banghead:

snb
06-22-2020, 03:09 AM
But what if you do not understand the help we offer ?

Sheepy1250
06-22-2020, 03:12 AM
But what if you do not understand the help we offer ?

It's all part of the great big learning curve I'm on right now!

Prepared to listen to any and all advice/guidance

BIFanatic
06-22-2020, 04:35 AM
Does this work for you?




Sub Test()

Dim LastRow As Long

With Sheet1

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("G1:G" & LastRow).Formula = "=IF(A1="""","""",RIGHT(A1,6))"
.Range("H1:H" & LastRow).Formula = "=IF(A1="""","""",RIGHT(A1,LEN(A1)-3))"
.Range("I1:I" & LastRow).Formula = "=IF(A1="""","""",LEFT(H1,LEN(H1)-7))"
.Range("J1:J" & LastRow).Formula = "=IF(A1="""","""",LEFT(G1,5))"

End With

End Sub

Paul_Hossler
06-22-2020, 06:35 AM
Or this




Option Explicit


Sub SplitData()
Dim r1 As Range, r2 As Range, c As Range
Dim s As String
Dim v As Variant

Set r1 = ActiveSheet.Range("A1")
Set r2 = r1.End(xlDown)

For Each c In Range(r1, r2).Cells

'no spaces
s = Trim(c.Value)

'leading plus's
Do While Left(s, 1) = "+"
s = Right(s, Len(s) - 1)
Loop

'trailing )'s
Do While Right(s, 1) = ")"
s = Left(s, Len(s) - 1)
Loop


'start at 0, i.e. v(0)
v = Split(s, "(")

'put town one col over, and numbers 2 cols over
c.Offset(0, 1).Value = Trim(v(0))
c.Offset(0, 2).Value = Trim(Left(v(1), Len(v(1)) - 1))
Next


End Sub

snb
06-22-2020, 07:23 AM
town name:


=IFERROR(MID(A1,4,LEN(A1)-10),"")

postal code:


=IFERROR(MID(A1,4,LEN(A1)-10),"")

In VBA:

Sub M_snb()
[C1:D2000] = [if(A1:A2000="","",if(mod(column(C:D),2)=1,mid(A1:A2000,4,len(A1:A2000)-10),left(right(A1:A2000,6),5)))]
End Sub

jolivanes
06-22-2020, 11:43 PM
Result in Columns B and C
Sub Maybe()

Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
c.Offset(, 1).Resize(, 2).Value = Array(Mid(c, 4, InStr(c, "(") - 5), Left(Mid(c, InStr(c, "(") + 1), Len(Mid(c, InStr(c, "(") + 1)) - 1))
Next c
End Sub

jolivanes
06-22-2020, 11:48 PM
@snb, Post #3.
I guess the idea of code "In layman's terms" comes to mind

snb
06-23-2020, 12:30 AM
@joli

Yes, but even if it's simple dutch, if you know nothing of dutch you won't understand anything.
There' no 'layman's' VBA.
Your solution is fortunately more VBA than #5. :whistle:

Sheepy1250
06-23-2020, 01:06 AM
I guess I might have oversold my VBA knowledge a little, guys - I understand the very basic functionality of some things so should be able to interpret the stuff you've very kindly offered!

Here goes nothing - thanks again everyone :)

BIFanatic
06-23-2020, 01:16 AM
#10

Haha, correct, I thought he just wanted to have VBA enter the formula's for him :D

Sheepy1250
06-23-2020, 01:43 AM
Or this




Option Explicit


Sub SplitData()
Dim r1 As Range, r2 As Range, c As Range
Dim s As String
Dim v As Variant

Set r1 = ActiveSheet.Range("A1")
Set r2 = r1.End(xlDown)

For Each c In Range(r1, r2).Cells

'no spaces
s = Trim(c.Value)

'leading plus's
Do While Left(s, 1) = "+"
s = Right(s, Len(s) - 1)
Loop

'trailing )'s
Do While Right(s, 1) = ")"
s = Left(s, Len(s) - 1)
Loop


'start at 0, i.e. v(0)
v = Split(s, "(")

'put town one col over, and numbers 2 cols over
c.Offset(0, 1).Value = Trim(v(0))
c.Offset(0, 2).Value = Trim(Left(v(1), Len(v(1)) - 1))
Next


End Sub




Hi Paul - thanks for this, it's almost perfect! Trying hard to locate where the final number of the string within the trailing brackets has managed to escape but my VBA knowledge isn't good enough to understand quite where yet - I'm going to hazard a guess that it may be somewhere within the final sub where the Trim command does its thing but not at all confident about that.

I am desperately trying to learn how to delve into the almost Cyrillic world of VB but am at the very start of my journey (if it's a journey then I'm just picking up the car keys in terms of where I've reached!!)

Thanks again though:)

Mark

p45cal
06-23-2020, 02:55 AM
Another cat losing its pelt:
Sub blah()
With Range(Cells(1), Cells(1).End(xlDown)).Offset(, 1).Resize(, 2)
.Columns(1).FormulaR1C1 = "=TRIM(MID(RC1,4,SEARCH(""("",RC1)-4))"
.Columns(2).FormulaR1C1 = "=MID(RC1,SEARCH(""("",RC1)+1,5)"
'.Value = .Value 'include this line to lose the formulae.
End With
End Sub

jolivanes
06-23-2020, 10:29 AM
Re: Your solution is fortunately more VBA than #5
Thanks for the trust. Still learning from a.o. your contributions.
In a lot of cases I tend to go for "slower" solutions, looping where it can be done without as an example, because I think that they would be able to change the code easier if needed.
Many roads lead to Rome, or even Breda, "de parel van het zuiden."
For those that unfortunately don't speak Dutch, that means "the pearl of the south" (south as part of the country).

Paul_Hossler
06-23-2020, 06:21 PM
Hi Paul - thanks for this, it's almost perfect! Trying hard to locate where the final number of the string within the trailing brackets has managed to escape but my VBA knowledge isn't good enough to understand quite where yet - I'm going to hazard a guess that it may be somewhere within the final sub where the Trim command does its thing but not at all confident about that.

I am desperately trying to learn how to delve into the almost Cyrillic world of VB but am at the very start of my journey (if it's a journey then I'm just picking up the car keys in terms of where I've reached!!)

Thanks again though:)

Mark


Didn't realize you wanted the Town number to go also. Look at the marked lines




Option Explicit




Sub SplitData()
Dim r1 As Range, r2 As Range, c As Range
Dim s As String
Dim v As Variant

Set r1 = ActiveSheet.Range("A1")
Set r2 = r1.End(xlDown)

For Each c In Range(r1, r2).Cells

'no spaces
s = Trim(c.Value)
's = "+++TOWN NAME 1 (LLLNN)"

'leading plus's
Do While Left(s, 1) = "+"
s = Right(s, Len(s) - 1)
Loop
's = "TOWN NAME 1 (LLLNN)"

'trailing )'s
Do While Right(s, 1) = ")"
s = Left(s, Len(s) - 1)
Loop
's = "TOWN NAME 1 (LLLNN"




'start at 0, i.e. v(0)
v = Split(s, "(")
'v(0) = "TOWN NAME 1"
'v(1) = "LLLNN"


'*************************************************
'trailing numbers and spaces
Do While Right(v(0), 1) = " " Or IsNumeric(Right(v(0), 1))
v(0) = Left(v(0), Len(v(0)) - 1)
Loop
'v(0) = "TOWN NAME"
'v(1) = "LLLNN"

'put town one col over, and numbers 2 cols over
c.Offset(0, 1).Value = Trim(v(0))
c.Offset(0, 2).Value = Trim(Left(v(1), Len(v(1)) - 1))
Next




End Sub