Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

loop through a range name

  Asked By: Bonni    Date: Nov 10    Category: MS Office    Views: 5192
  

i have several data on column D from cell 5 up to 100.
i call this range(D1:D100) as scada_ckt.
D1 contains: 45XT48
D2 contains: 24XP5

Scenario is:

1. I want to read D1 and transform it into
several values as 45; XT; and 48. That is
E1 will contain 45
F1 will contain XT
G1 will contain 48

2. The loop should continue up to D100

How do I do it using VBA?

Share: 

 

12 Answers Found

 
Answer #1    Answered By: Ava Campbell     Answered On: Nov 10

I should do that in a worksheet with the function: Mid

e.g. in
E1: Mid(D1;1;2)
F1: Mid(D1;3;2)
G1: Mid(D1;5;2)

Look in the help for help on the MID-function

 
Answer #2    Answered By: Brenda Fischer     Answered On: Nov 10

I believe you have wrongly interpreted my
question. You see the division is variable.
That is:
E1 will contain the numeric character
F1 will contain the alphabetic characters after it
G1 will contain the last numeric afterwards
Example:
D E F G
1 45XT48 45 XT 48
2 24XP5 24 XP 5
3 2TYE456 2 TYE 456
4

Therefore, the mid won't work

 
Answer #3    Answered By: Tasha Wheeler     Answered On: Nov 10

You are right. Is there a limit to the number of the divisions?

 
Answer #4    Answered By: Gilbert Moore     Answered On: Nov 10

Try something like;

Public Sub StrTest()
Dim Strng As String, aChr As String, i As Integer, FirstStr As Boolean
Dim str1 As String, str2 As String, str3 As String

Strng = "123XYZ24"

str1 = "": str2 = "": str3 = "": FirstStr = True
For i = 1 To Len(Strng)
aChr = Mid(Strng, i, 1)
If InStr("0123456789", aChr) > 0 Then
If FirstStr Then str1 = str1 & aChr Else str3 = str3 & aChr
Else
FirstStr = False
str2 = str2 & aChr
End If
Next i

Debug.Print Strng, str1, str2, str3

End Sub

 
Answer #5    Answered By: Adalwolfa Fischer     Answered On: Nov 10

In the previous message, the "FOR" statement was added to the previous line.

Use the example below. (For some reason, Reply, formats the code properly)

 
Answer #6    Answered By: Rebekah Walker     Answered On: Nov 10

I modified
the code to something like:

========
Sub DivBreakerCode()

Dim z As Long
Dim fdr, xtrchr, strckt As String
Dim fdrNum, fdrCode, fdrSide As String
Dim fdrNumFlag As Boolean

fdrNum = vbNullString
fdrCode = vbNullString
fdrSide = vbNullString
strckt = vbNullString
xtrchr = vbNullString

'MsgBox ActiveCell.Offset(0, -4)
strckt = ActiveCell.Offset(0, -4).Value

'MsgBox strckt

'Step thru each digit in cell
For z = 1 To Len(strckt)
xtrchr = Mid(strckt, z, 1)
If IsNumeric(xtrchr) Then
If fdrNumFlag Then
' get bkr_Side
fdrSide = fdrSide & xtrchr

Else
'MsgBox "Start Feeder Number"
fdrNum = fdrNum & xtrchr
End If

Else
fdrNumFlag = True
'MsgBox "Start Feeder Code"
fdrCode = fdrCode & xtrchr
End If
Next z 'Next digit in cell

'ActiveCell.Offset(1, 0).Select
'ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(0, 0).Value = fdrNum & fdrCode
ActiveCell.Offset(0, 1).Value = fdrCode
ActiveCell.Offset(0, 2).Value = fdrSide

End Sub

========

 
Answer #7    Answered By: Fadwa Ahmed     Answered On: Nov 10

Another option is to use a function to find where the alphabetic
characters begin and end within the string. The advantages are the
method is available for use by persons with no real programming ability.




(The direction input simply tells the function whether you're looking
for the first alphabetic character from the default left [1 or any
character other than 2] or the first alphabetic character from the right
[2]





Function FirstLetter(cell As String, Direction As Double)

Dim character() As String

Dim s As Integer, i As Integer



s = Len(cell)

ReDim character(1 To s) As String



If Direction = 2 Then

For i = s To 0 Step -1

character(i) = Mid(cell, i, 1)

If Asc(character(i)) < 48 Or Asc(character(i)) > 57 Then

GoTo EndLoop

End If

Next i

Else

For i = 1 To s

character(i) = Mid(cell, i, 1)

If Asc(character(i)) < 48 Or Asc(character(i)) > 57 Then

GoTo EndLoop

End If

Next i

End If

EndLoop:



FirstLetter = i



End Function

 
Answer #8    Answered By: Bin Fischer     Answered On: Nov 10

One thing to be careful of is not limiting your options. In your example,
the code is limited to numbers before or after a text string. The following
is an extension of the previous example that will handle a code that can be
split into ten parts. nnnXXnnXXXnXXXXnnnnnnXXXnX

Public Sub SplitCode()

Dim Strng As String, aChr As String
Dim i As Integer, idx As Integer
Dim str(10) As String

Strng = "123XYZ24"

For i = 1 To 10: str(i) = "": Next i
idx = 0

For i = 1 To Len(Strng)
aChr = Mid(Strng, i, 1) ' find the character
If InStr("0123456789", aChr) > 0 Then ' is it numeric
If Int(idx / 2) * 2 = idx Then idx = idx + 1
Else
If Int(idx / 2) * 2 <> idx Then idx = idx + 1
End If
str(idx) = str(idx) & aChr
Next i

Debug.Print Strng
For i = 1 To 10: Debug.Print str(i): Next i

End Sub

Another thing to be careful of is using the ASC function without a comment
that explains what you are looking for. If your code was moved to a
mainframe, your code would need to be converted to use 'F0' to 'F9'.

 
Answer #9    Answered By: Rudy Turner     Answered On: Nov 10

using your code one can create a function that will pull out any
block of consecutive numeric or alphabetic characters. The first
limitation I've found however is that the code requires the string input
to begin with numeric data.



___________________________________________________________



Function CharStrings(ByVal cell  As String, group As Double)



Dim Strng As String, aChr As String

Dim i As Integer, idx As Integer

Dim str(10) As String



Strng = cell



For i = 1 To 10: str(i) = "": Next i

idx = 0



For i = 1 To Len(Strng)

aChr = Mid(Strng, i, 1) ' find the character

If InStr("0123456789", aChr) > 0 Then ' is it numeric

If Int(idx / 2) * 2 = idx Then idx = idx + 1

Else

If Int(idx / 2) * 2 <> idx Then idx = idx + 1

End If

str(idx) = str(idx) & aChr

Next i



CharStrings = str(group)



End Function

 
Answer #10    Answered By: Zivah Levi     Answered On: Nov 10

I had thought of that and the code I did not add would have checked the
first character and set idx to 1 if the first character was not numeric.
This would leave the first element of the array empty, but that is easy to
take care of later. Of course, leaving the empty element means that you will
know that all the odd elements are numbers and all the even ones are
characters.

What is the group variable for? You may want to check your Function
definition and how you are passing the variables back.

 
Answer #11    Answered By: Shayne Anderson     Answered On: Nov 10

The group variable just allows the user of the function to select which
blocks of array elements they want shown in a particular cell.



Using column  headers of 1,2,3...10 with the alphanumeric strings listed
down a column, the user can fix the header and row references to extract
the different blocks of characters within the strings.



Using your example [123XYZ24], group 1 would correspond to "123", group
2 corresponds to "xyz" and group 3 to "24".

 
Answer #12    Answered By: Ludano Ricci     Answered On: Nov 10

In other languages, Str is used to convert a number to
a string. I'm also use to seeing the Function definition end with "As
String".

 
Didn't find what you were looking for? Find more on loop through a range name Or get search suggestion and latest updates.




Tagged: