Replies

Rudi:
From my own use of Excel, when I use links I much prefer variable links. I have not experienced added problems with variable external links and they seem to operate just as well.

Recommendation – When dealing with a modest number of external links I prefer to name the cell in the source workbook and use that name as the variable in the target workbook. Also in the Target workbook I change the font color of links to make it easier to spot linked data. Lastly adding a comment to the target cell can make later modifications easier.

TD

If anyone needs a fast way to add sequential numbers to a large amount of rows in a list or data base the marco below can be a real time saver over custom fill.

Select an empty column next to the data highlight the rows you want and run the Maco. Will work in both directions, either selecting using the up direction or selecting using a down direction.

Having the numbers is great for restoring data to its original order after custom sorting, or other types of data movements.

TD

Sub NumFil()
‘
‘ NumFil Macro

‘Macro will allow a user to select cells made up
‘of a single column that will then be filled with
‘sequential numbers
‘
Dim rCount As Integer
Dim r As Integer

rCount = Selection.Rows.Count
Selection.Cells(1, 1).Activate
Selection.Cells(1, 1).Value = 1
For r = 1 To rCount – 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = _
ActiveCell.Offset(-1, 0).Value + 1
Next
‘
End Sub

To make certain what is needed can you post an Excel Worksheet with a line or two of what the final solution would be. Also apply a different color to the added information.
That could help in finding a solution.

Tom Duthie

Chuck:

I have attached a proposed solution. See the Blue wording in the attached.

I recommend the VBA Macro for the last part of the solution but it can be done without VBA but it will take a L O N G time.

Good Luck

Tom Duthie

Hope this helps.

Tom Duthie

Thanks for the excellent suggestions and timely response
Tom Duthie

Edited by HansV to remove large number of superfluous empty lines.

Dear Mark and Hans:

With you suggestions and our determination the following code works in all situations. It may not be the best code but it works

Sub Form_Current()
On Error Resume Next
Dim rst As Recordset
Dim lngCount As Long
Dim Count As Integer
Dim rsFind As Recordset

‘This code displays the “Record # of #” label on my form
Set rst = Me.RecordsetClone
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordCounter = “Record ” & Me.CurrentRecord & ” of ” & lngCount

‘The rest of this code loops until the values in txtTemp and txtIssueID match in a given recordset (essentially a find function). Every

The book I like best is “Learn Microsoft Excel VBA 2000 programming” by Julitta Korol. Also there is a VBA for Dummies, I have not read it but I would assume it would be very similar to all other Dummy books.

Once you have looked through these items; the next thing I would recommend is searching the lounge for code you may need for a project..

Good luck

Regards,

TD

Christina:

Make sure the pointer is not inside the control box but touching the edges. On my version of Excel the pointer turns into a black line with double arrows.
When you have the proper pointer right click and you should be able to assign you Macro.

Hint, change Zoom To 200% or even greater so you can better control the pointer.

Tom Duthie

Rudi:
A comprehensive answer to the problem you raised can be found in “Writing Excel Macros” Authored by Steven Roman Published by O’Reilly. In addition the book is a good Excel VBA guide which I have used on several occasions to enhance my Excel VBA procedures.

I am posting this mostly for the benefit of other Loungers that may read this post.

Regards,
Tom Duthie

Hans:

Just what I was searching for !
Not having to move the range to another worksheet or to the top of the active worksheet is a real time saver.

Tom Duthie

Hans:

Thank you explaining the error. Of course you are correct concerning VBA, my version of Excel was just recently upgraded to 2003. This is just another default that I have yet to reset in Excel on the 2003 version.

Regards,

Tom D

Hans:

Thank you explaining the error. Of course you are correct concerning VBA, my version of Excel was just recently upgraded to 2003. This is just another default that I have yet to reset in Excel on the 2003 version.

Regards,

Tom D

Cindy:
Hans is correct about the way Excel handles page breaks. But it could be possible to create a MACRO using Visual Basic For Applications and the Page Set Up propeties in Excel to have Excel automate the process of putting in page breaks based on the logic placed in the MACRO. However, the macro would take time to create and debug and that time should be considered against how often you spend time with page break issue.

Regards,

TD

Hans: Thank you for confirming my observation.

On my version of Excel 2002 if you View > Custom Views and then click the question mark (help) and select the Add button; Microsoft once again declares that rows and columns are part of view, however as you have stated this is not true.

Regards,

TD

Hans: Thank you for confirming my observation.

On my version of Excel 2002 if you View > Custom Views and then click the question mark (help) and select the Add button; Microsoft once again declares that rows and columns are part of view, however as you have stated this is not true.

Regards,

TD