Quick A's to easy(?) Q's appreciated...

[astro_al]

Hi, I need a few quick pointers if possible. I'm trying to create a
visualbasic routine which will format a text file imported into excel.

The text files represent a small database with about 5 columns, but
fairly long - up to about 100 pages.

After importing, each line (row) is in the leftmost column of the
spreadsheet (ie each line is in a single cell), the data is not
delimited in any useful way.

My questions are as follows:

1. How can I delete an unknown number of spaces (varies) from the
beginning of the text string in each cell?

2. How can I find out the coordinates of the selected cell - ie if I
'Find' in a column and it stops when it finds what I'm looking for, how
can I read in the value of the row number?

3. My general approach is going to be a loop which takes each 'line',
ie cell in the first column, in turn and compares the first part to a
set of if statements, for example, the beginning of the line might say
'STAGE "...' so it'll run through until it finds the if statement
saying 'STAGE"...' and operate on the rest of the line accordingly
(make sense?). There are only about 20 things that each line might
start with, so this seemed the easiest way.

Is there an easier way?!

Cheers for any help, Al.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Dave Peterson]

I think David meant:
newStr = LTrim(oldStr)
(excel itself doesn't have LTrim--it's a VBA function.)

But
newstr = application.trim(oldstr)
Will get rid of leading/trailing/duplicated internal spaces. (What I bet David
was thinking.)

And you may want to look at .Find.

This is close to what's in the Help.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim WhatToFind As String

WhatToFind = "Stage"
With ActiveSheet.Range("a:a")
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox WhatToFind & " was not found!"
Else
MsgBox FoundCell.Row
End If

End With

End Sub

 

[astro_al]

Thanks a lot for the really useful input guys, its starting to work now,
I still have a few queries though:

1. I have a counter for the row number (imaginatively entitled
'COUNT1') that I am trying to use to select a row. If I record a Macro
in Excel and in it select a row to delete it gives me (with row 5
selected):

Rows("5:5").Select
Selection.ClearContents
Selection.Delete Shift:=xlUp

This does what I want (not complex), but I want it to be variable with
the value of COUNT1, so I thought:

Rows("COUNT1:COUNT1").Select
Selection.ClearContents
Selection.Delete Shift:=xlUp

But no go - any ideas? What I'm actually trying to do is select rows
which have no text in (only 1 column, so easy using the IsEmpty
function) and remove that row so I dont have any random empty rows
through the document.

2. Whats the function which gives the value of the last occupied cell
in the sheet (I need to loop until the final line of text for documents
of different size, whats the easiest way of stopping the loop at the
end of the rows of text)?

3. I have taken your advice and will be using 'Case...'. Whats the
best way (now I have no spaces at the beginning of the lines!) to
select the FIRST WORD of each line - I can guarantee that each word is
followed by a single space. I want to select the first word, then
compare this with various cases and operate on the rest of the line
according to the matching Case - some have just text, which is easy,
some have numbers to extract for which I intend to use the FIND and MID
functions.

4. The 'activecell.address' works well, if I just want the row number
should I use the function which selects the numeric part of a string to
extract the row number? i.e. if

activecell.address = $A$1

then I just want the '1' part - whats the easiest way to get it, or
just the row number with another function?

Sorry for all the simple questions, its been a long time since I used
VB and my brain has muddled it with Matlab, FORTRAN etc which can be
similar. If its any use in inspiring answers you are helping to
control spacecraft with the info you give me!

Thanks again - all help genuinely appreciated.

Al.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[David McRitchie]

Hi Al,
This will delete rows for which a cell in the selection area is blank.
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Same thing but the area will be Column A

Sub del_COLA_empty()
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

More of same see
http://www.mvps.org/dmcritchie/excel/delempty.htm

#2 the last cell identified by Ctrl+End could itself be empty
.http://www.mvps.org/dmcritchie/excel/lastcell.htm

perhaps you mean in a column.
http://www.mvps.org/dmcritchie/excel/toolbars.htm

Sub GotoBottomOfCurrentColumn()
'Tom Ogilvy 2000-06-26
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
End Sub

If you want the last used cell in a row
Cells(ActiveCell.Row,Columns.Count).End(xlLeft).Select
or the empty cell to the right of the last used cell in a row
Cells(ActiveCell.Row,Columns.Count).End(xlLeft).offset(0,1).Select

#3 you would use a trim and then FIND the first space and subtract
1 and use the LEFT function. The equivalent in WS Function
=IF(LEN((trim(A11))=0,"",IF(ISERR(FIND(" ",Trim(A11))),Trim(A11),LEFT(trim(A11),FIND(" ",trim(A11))-1)))

#4 Activecell.Row

Our answers are posted directly to the actual Excel newsgroups not
through another entity.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

1. [...] n What I'm actually trying to do is select rows
which have no text in (only 1 column, so easy using the IsEmpty
function) and remove that row so I dont have any random empty rows
through the document.

2. Whats the function which gives the value of the last occupied cell
in the sheet (I need to loop until the final line of text for documents
of different size, whats the easiest way of stopping the loop at the
end of the rows of text)?

3. I have taken your advice and will be using 'Case...'. Whats the
best way (now I have no spaces at the beginning of the lines!) to
select the FIRST WORD of each line - I can guarantee that each word is
followed by a single space. I want to select the first word, then
compare this with various cases and operate on the rest of the line
according to the matching Case - some have just text, which is easy,
some have numbers to extract for which I intend to use the FIND and MID
functions.

4. The 'activecell.address' works well, if I just want the row number
should I use the function which selects the numeric part of a string to
extract the row number? i.e. if

activecell.address = $A$1

then I just want the '1' part - whats the easiest way to get it, or
just the row number with another function?

Sorry for all the simple questions, its been a long time since I used
VB and my brain has muddled it with Matlab, FORTRAN etc which can be
similar. If its any use in inspiring answers you are helping to
control spacecraft with the info you give me!

Thanks again - all help genuinely appreciated.

Al.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------