Posts by mumphis

Re: Offset A Range When Last Row Is Blank

perfect, many thanks.

given a range named xyz which refers to d1:e3

range("xyz")

where the 3rd row is blank.

I want to examine the row after the range with

range("xyz").end(xlUp).Offset(1,0).Cells(1,1).value

I find that this code refers to the last row of the range which is blank.

How can I be sure that I will be 1 row (offset=1) below the range?

Re: Merge Cells In An Offset Range

Perfect, Thanks!

I find the first iteration of this loop (counter=0) works fine. Subsequent iterations work EXCEPT the .MergeCells. Is there a better way to approach this?

The .font and .value behave correctly.

For counter = 0 To 4    


            With Workbooks(wb).Worksheets(ws).Range("a4:f4").Offset(counter, 0) 
                .MergeCells = True
                .HorizontalAlignment = xlCenter
                With .Font
                    .Bold = True
                    .Name = "Arial"
                    .Size = 14
                End With
                .Value = Workbooks("MY.xla").Worksheets(1).Cells(counter + 2, 1)
            End With
        Next counter

Re: Proper Case Text By Condition

Thanks for all the replies, cheecky or otherwise. The data is supplied to me, I can not control the source(s).

I will use a macro to handle the situation.

.... and Stewie will Never take over the world (unless he learns Excel and VBA)....

How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).

There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.

I'm assuming the data was supplied in uppercase

Many thanks.

I would like to hear about products or methodologies related to your development environment. For instance, is there a 'compare' or 'diff' available so that I can compare my current VBA module (local to my machine)to the same module which is currently in production.

How are larger projects managed when there are multiple developers? Is there any way to 'check out' a module from the production environment so that others are locked out of changing it while I develop code in the module?

Re: Keep Format When Copying From Range To Bookmark

MacroMike,

The .text qualifer did the trick, many thanks. :music:
In excel, I defined names that are the same as the Word bookmarks.

I am looping through all of the bookmarks in a document. There is a Excel name which is the same as the bookmark. I would like to retain the Excel format.

numBookmarks = ActiveDocument.Bookmarks.Count
    For counter = 1 To numBookmarks
        bmk = ActiveDocument.Bookmarks(counter).Name
        Selection.GoTo What:=wdGoToBookmark, Name:=bmk
        Selection.TypeText Format(myWB.Sheets("Job Sheet").Range(bmk), "00000")   
    Next counter

In the example, I have hardcoded the "00000" format for a U.S. Zipcode. Other defined names could be for dates, dollar amounts, etc.

Is there a way to use the format found in Excel for that defined name.

Re: Sort Parsed Array

Thanks, that is exactly what I did.:smile:

I have an array as follows in the form of 'lastname number date'

lastname - could be one or two names
number - could be 4 or 5 digits
date - YYYYMMDD

e.g.

Jones 1234 20061010
Smith 1255 20061105
Robinson 1288 20030404
Frederick Stevenson 1222 20040808

I would like to sort by the number or date. There is always a blank in between the 3 pieces of data. I thought of using split to get the data, then bubble sort it and force the other pieces to follow along as they bubble up or down. It seems cumbersome.

what would you suggest?

Re: Date Format Listbox Project Library Not Found

Yes, my last checked item:

MISSING: wbocx ActiveX Control module

Where or how would I find it?

When running this on Excel 2003, I receive a:
Can't find Project or Library error message.

It had been working on Excel 2000.

Worksheets("W").OLEObjects(lbN).Object.AddItem  Format((Range(N).Value(1, 1)), "H:MM AM/PM")

Would anyone know what library I need to reference or is there a better way to format it for the listbox?

Many Thanks,

Re: Activex Listbox - Assigning The Selected Row

Thanks for the 2 replies. I figured it out. My error was that I had a ActiveX listbox control and a defined name that had the same name (they weren't called 'listbox1' in my code

never would have thought there would be a conflict....

My ActiveX listbox looks like:

AB
RV
TP
SK

After loading the listbox via .additem, I want to set one of the rows as 'selected'. I have been trying with variations of the following without success.

Worksheets("J1").OLEObjects("listbox1").Object.Selected(Counter) = True

What is the correct syntax? I am using Excel 2000.

I have a .txt file created in Notepad and it contains a 4 digit number (Job ID). I am recieving the dreaded "Bad File Mode" (#54) error when writing back the file.
I would like to OPEN it as R/W locked, read it, increment it by 1 and write it back. Am perfectly willing to create the .txt as non-binary if that is causing the issue. Any suggestions would be greatly appreciated.
:confused:

Dim jinChar As String
    fileNumber = FreeFile()
    jinFP = "JIN.txt"
    Open jinFP For Binary Access Read Write Lock Read Write As #fileNumber
    Input #fileNumber, jinChar
    jobIDNumber = CInt(jinChar)
    jobIDNumber = jobIDNumber + 1
    jinChar = CStr(jobIDNumber)
    Write #1, jinChar    
    Close #fileNumber

Re: File Locking A Flat File Over The Network

to answer both Dave and Sicarii,

We use Excel 2003 and the file size is tiny. It literally stores 1 number which represents the next available 'Job ID number' which is a 6 digit number. The goal would be to: lock, read, increment the job id, write it back. All clients would make their lock attempt in a loop, i.e. attempt to lock, if fail, wait a second, try again.

There has been no corruption yet as I have not even coded this yet. I posed the question because I had heard that applications 'in general' can have corruption issues with RW over a network. Maybe its more of an issue if an app wanted to maintain an open RW connection for an extended period of time? If this is something the Excel/VBA community rarely or never sees, then all the better.

I am not a networking expert but do know that their network is newly installed.

Thanks to Dave and Sicarii for jumping in...

Re: File Locking A Flat File Over The Network

I was concerned about corruption over the network. If it is a non-issue, all the better. Only 1 client should be able to:
lock, read, increment the job id, write it back at a time.

Thanks,
mumphis

I have a simple flat file that stores a 'next Job ID number' through VBA. It is stored on a network drive which can be accessed by clients in different physical locations.

Are there any known issues with outside clients locking the file as 'Lock Read Write'. Is this prone to data corruption? Is there a set of steps that should be followed to keep the file from being corrupted?