Looping thru all rows with instr() test
 

Hi,

I'm a VB6 developer, but I am battling to get to grips with this. I
have a spreadsheet with two columns (A & B). In column A, is a folder
name, embedded in which is the user name and in column B, is an amount.


I need to loop for all rows, and starting in a fixed position in column
A, derive the user name and put that in column C. However, the username
is not fixed length.

Can someone PLEASE help as I have been trying for a good few hours and
not getting very far.

Thanking you!


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586

Looping thru all rows with instr() test
 

Need more info: What is the format of the folder name and what is the
standard form of the user name. In other words, given that you begin looking
in a fixed position, what logic can be followed to find the end of the user
name?
--
- K Dales


"Silvertip" wrote:


Hi,

I'm a VB6 developer, but I am battling to get to grips with this. I
have a spreadsheet with two columns (A & B). In column A, is a folder
name, embedded in which is the user name and in column B, is an amount.


I need to loop for all rows, and starting in a fixed position in column
A, derive the user name and put that in column C. However, the username
is not fixed length.

Can someone PLEASE help as I have been trying for a good few hours and
not getting very far.

Thanking you!


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586

Looping thru all rows with instr() test
 

In C2 enter =MID(A2,12,LEN(A2)-11)
and copy down

where the 12 above is the starting position (unchanging) and the 11 is
1character Less (12-1).

HTH
Jim


"Silvertip" wrote
in message ...

Hi,

I'm a VB6 developer, but I am battling to get to grips with this. I
have a spreadsheet with two columns (A & B). In column A, is a folder
name, embedded in which is the user name and in column B, is an amount.


I need to loop for all rows, and starting in a fixed position in column
A, derive the user name and put that in column C. However, the username
is not fixed length.

Can someone PLEASE help as I have been trying for a good few hours and
not getting very far.

Thanking you!


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile:
http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586

Looping thru all rows with instr() test
 

in xl2k and higher, there is a split function that can separate text into its
pieces (based on a delimiter).

If your path is always the same format:

x:\path\path\path\username\path\path\path

you could split the string and pick out the 4th component:

dim myStr as string
dim myArr as variant
dim myName as string
mystr = "x:\path\path\path\username\path\path\path"
myarr= split(mystr,"\")
myname = myarr(4)
msgBox myName





Silvertip wrote:

Hi,

I'm a VB6 developer, but I am battling to get to grips with this. I
have a spreadsheet with two columns (A & B). In column A, is a folder
name, embedded in which is the user name and in column B, is an amount.

I need to loop for all rows, and starting in a fixed position in column
A, derive the user name and put that in column C. However, the username
is not fixed length.

Can someone PLEASE help as I have been trying for a good few hours and
not getting very far.

Thanking you!

--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586

--

Dave Peterson

Looping thru all rows with instr() test
 

Filename User
Field3
winamp3_0-full.exe Z:\Data\Users\ashcrofn\data\
3510536
moons.mp3 Z:\Data\Users\ashcrofn\My music\ 3571965
In Africa.mp3 Z:\Data\Users\ashcrofn\My music\ 3919289
Aerosmith Z:\Data\Users\ashcrofn\My music\ 4757504

Hi Dave,

I like the method that you utilise for extracting the name out of the
user column as it should work.

Included in the sample source that you gave me, bearing in mind the
sample data above, how do I:-

loop for all rows in the dataset ?
Take the value from the array and write it into another column on the
same row, for all rows ie as I loop ?

Many thanks.


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586

Looping thru all rows with instr() test
 

It kind of looks lie the folder name is in column B. (Adjust the code if that's
wrong.)

Dim FirstRow as long
dim LastRow as long
dim iRow as long
dim myStr as string
dim myName as string

with worksheets("sheet1")
firstrow = 2 'headers in row 1???
lastrow = .cells(.rows.count,"B").end(xlup).row

for iRow = firstrow to lastrow
myStr = .cells(irow,"B").value
myname = yourroutinetosplitMYSTRintopieceshere
.cells(irow,"E").value = myName
next irow
end with




Silvertip wrote:

Filename User
Field3
winamp3_0-full.exe Z:\Data\Users\ashcrofn\data\
3510536
moons.mp3 Z:\Data\Users\ashcrofn\My music\ 3571965
In Africa.mp3 Z:\Data\Users\ashcrofn\My music\ 3919289
Aerosmith Z:\Data\Users\ashcrofn\My music\ 4757504

Hi Dave,

I like the method that you utilise for extracting the name out of the
user column as it should work.

Included in the sample source that you gave me, bearing in mind the
sample data above, how do I:-

loop for all rows in the dataset ?
Take the value from the array and write it into another column on the
same row, for all rows ie as I loop ?

Many thanks.

--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586

--

Dave Peterson

Looping thru all rows with instr() test
 

I'm sorted - thanks!


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480586