Posts by Jeff P

Perfect!

..Thank you very much.

Cheers!

I'm in need of a script that increases a single increment from the upper value within blank rows.

To go from this: ( in Column "D")

_000

_145

_153

_179

_298

_400

....to this:

_000

_001

_002

_003

_145

_153

_179

_180

_181

_182

_183

_298

_299

_300

_301

_302

_303

_400

Thanks!

Jeff

Thank you very much, KjBox.

Works Great!!

Cheers!

I have a named Range called "SEQ" in Column C that spans about 4000 rows.
Within this Range are various markers. "x"
New rows are routinely added.
I'm in need of a VBA script that will add a numerical sequence between these markers, in this named Range.

-- from this:

C
x

x

x

x

---to this.
C
x
1
2
3
x
1
x
1
2
3
4
x

Many thanks!

Re: offset from first coulmn

Thanks, Smallman!

Good to know!

Re: offset from first coulmn

Hello, Pike

Thank you very much. Works Great!!

Cheers!

Jeff

Re: offset from first coulmn

Thanks for the replies.

Pike, thanks for the code, but still offsets the x value to the right of the word itself.

Smallman, Thanks! I've uploaded a sample file of what im trying to achieve. there are two sheets: a before and after result, if that makes sense.

The code searches for the text in A1 "time" from a named range "Outline_07", then offsets an "x" mark 4 columns from the left, if it appears.

Thanks for the help.
Jeff

Hello

I came across this script, which I've modified
It searches for a specified name from "A1", within the range "Outline_07"
and offsets an "X" mark 13 cells to the right, of where it appears in that row.

It works great.

But is it possible to modify this such that it no longer offsets the "X" mark from where the name appears in the row, but rather places the "X" mark always in Column 10.

Tried countless variations but cant get it to work!...
Thanks for any help.

Sub FindText_PlaceX()
    Dim Rng As Range
    Dim str As String
    Application.ScreenUpdating = False
    Set Rng = Range("Outline_07")
    str = Range("A1").Value
    If str = "" Then Exit Sub
    For i = 1 To Rng.Rows.Count
        If Rng.Cells(i).Value Like "*" & str & "*" Then
            Rng.Cells(i).Offset(0, 13) = "X"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Re: Word count in cell

Terrific!

Thank you very much!

Hello,

I came across this code which counts the number of words within a selection.

Works great!

Sub CountWords()
    Dim MyRange As Range
    Dim CellCount As Long
    Dim TotalWords As Long
    Dim NumWords As Integer
    Dim Raw As String


    Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)
    TotalWords = 0
    For CellCount = 1 To MyRange.Cells.Count
            If Not MyRange.Cells(CellCount).HasFormula Then
                Raw = MyRange.Cells(CellCount).Value
                Raw = Trim(Raw)
                If Len(Raw) > 0 Then
                    NumWords = 1
                Else
                    NumWords = 0
                End If
                While InStr(Raw, " ") > 0
                    Raw = Mid(Raw, InStr(Raw, " "))
                    Raw = Trim(Raw)
                    NumWords = NumWords + 1
                Wend
                TotalWords = TotalWords + NumWords
            End If
        Next CellCount
        MsgBox "There are " & TotalWords & " words in the selection."
End Sub

Is it possible to modify this such that instead of displaying the word count via a message box, it shows only the number result (not the text) within a cell / named range?

lets says: Range ("WordCount")

Thanks for any help!

Re: Find Text add value

Wow, this works perfectly!

I appreciate the time you put into writing this.
Thank you very, very much, sktneer!!

Cheers!

Jeff

Hello

Im in need of a vba macro which will find text within a column and indicate which row contains this text.

As an example: in A1 a key word is typed in ( which will vary), and in column A14:A500 I have a list of paragraph text information in each row.

The macro will search column A14:A500 for the name that's typed in A1.. if it finds it, offsets an "X" , 3 columns to the right... if it doesn't find it.. it does nothing.

Any ideas how to create this?

Many Thanks!

Re: Export to separate txt files

Terrific!

Thank you very ,very much, apo.

Works like a charm!.

Cheers!

Jeff

Re: Export to separate txt files

Thanks apo

Yes they will always be numerically ascending.. and may look something like this

Heading01_00.00
Heading01_01.00
Heading01_02.00
Heading01_03.00
Heading01_03.50
Heading01_03.55
Heading01_04.00

etc.

and the format will never get past 2 digits.

Thanks again for the help!

Jeff

Re: Export to separate txt files

Thanks for the reply, snb

If my worksheet heading list is this:

Heading01
Lorem ipsum dolor sit amet, no soluta apeirian pri.
Has luptatum voluptatibus te, tale adhuc fierent

Heading02
No laoreet neglegentur eam, ut sumo discere voluptua vim.
feugait his, primis aliquip sit te

Heading03
Viderer ornatus dolorum vel te. Id etiam
Est persecuti percipitur te

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

It will create 3 text files with their respected contents.
Heading01.txt
Heading02.txt
Heading03.txt

..but each text file will also include their numerical increment as the first line.

01
Lorem ipsum dolor sit amet, no soluta apeirian pri.
Has luptatum voluptatibus te, tale adhuc fierent

02
No laoreet neglegentur eam, ut sumo discere voluptua vim.
feugait his, primis aliquip sit te

03
Viderer ornatus dolorum vel te. Id etiam
Est persecuti percipitur te

Hope that helps..

thanks!

Re: Export to separate txt files

Hello..

This code works great John!, But Im wondering if it could be possibly modified to incorporate underscores within the heading?

I've needed to change my heading titles, whereupon the script no longer work when there's an underscore within the title heading.

Is it possible for this script to work for headings such as this?
Heading01_01.00
Heading01_02.00
Heading01_03.00
Heading01_04.00

Also thanks to snb who also provided a working code..( which does allow for underscores!, ...but it also includes the increment at the head of the body of text in the new file..which is undesirable.)

Thanks for any help.

Jeff

Re: Export to separate txt files

Wonderful!

Works great..thank you very much, John!

Jeff

Is there a macro which will allow me to export any text between the words "Heading" into their own .txt document? (while keeping the proper heading name as the text file?)
As an example. from this:

Heading01
definiebas pro. Sea ne option erroribus. Duo ut meis tamquam appa

Heading03
Ea zril explicari definiebas pro. Sea ne option errori
ne option erroribus. Duo ut meis tamqua

Heading08
explicari definiebas pro. Sea ne option erroribus. Duo ut meis tamqua

---
to this..
output to separate text file in C:\Users\JeffP\Documents\Temp

Heading01.txt
Heading03.txt
Heading08.txt

Thanks for any help..!

Jeff

Re: Remove BOM characters

Thanks pike.. couldn't get either one to work.. getting an error upon importing.

However I managed to fix my problem. Discovered my text files were somehow being saved as UTF-8. Manually re saved each file individually as ANSI .. a major pain but it worked.

Thanks for the help!

Jeff

When I import a text file its also importing BOM characters ie: 

Which is giving me errors with other macros.

How can I modify the following code to exclude these character markings?

Thanks for any help

Jeff

Sub ImportTextFile_00()
    Set myTextFile = Workbooks.Open("Z:\ImoprtFiles\Text_00.txt")
    myTextFile.Sheets(1).Range("B2").CurrentRegion.Copy _
         ThisWorkbook.Sheets(1).Range("B2")
myTextFile.Close (False)
End Sub