Posts by rpaulson

Hello all,

I would like to get the column value of the top left corner.

Private Sub CommandButton1_Click()
Cells(1, 1) = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column
End Sub

Any Ideas?

Ross

Re: Adding Rows To One Tab That Automatically Add And Update On Another

Paul,

I read you question (a few times), If I understand correctly, I think you may be wanting to use a pivot table. I think you should keep all the raw data in one sheet and then make a pivot table with classes.

If you can attach a small sample it would help.

Ross

Re: Building an SQL String

Huntersh,

When I want to get data from one workbook to another I will usually dump the data from the first book to a text file - then import the data from the text file into the 2nd workbook.

Hope this help,

Ross

Re: Dropdown List To Select Two Cells At Once

Is this what you are looking for?

Re: Heading Names In Criteria

Quote from AAE

Why not post a sample copy of your file?

Attached is a small sample file, Feel free to give a try.

Thanks,

Ross

Hello All

Cell A1 is HEAT_CODE
Cell A2 is W5H

In cell
=DMIN(Database,"Cost",A1:A2) return the proper value

I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)

the above returns #VALUE! error.

Anyone have any ideas?

Thanks,
Ross

Re: Match Numeric Data With Query Text Data

Quote from shg

All well now?

I believe so. I'll check again later as I am in a different location right now.

Re: Match Numeric Data With Query Text Data

I can change it to text. But it did not work until I saved my file and re-opened it. (Strange)

Txs,

Ross

Hello,

I have some code that goes through some data that is imported from a database via a query.
This has been working for a few years without a problem.

I lookup and employee number on my sheet to the employee number in the database table.

this is my code that checks the employee number.

Set rs = Worksheets("Employee")
If rs.Cells(r, 2) = cells(1,1) Then 'if employee number matches
......

The problem is that now the database application has adapted an alpha option (it used to be numeric only). In order for my code to work I have to change the employee number on my sheet by putting a ' in front of it.

Example if the number was 127 I need to enter '127 in the cells.

I have a few thousand in my data.

Can I add some code to my macro so I can still enter just the number in the cells?

Thanks,

Ross

Hello all,

I have an auto filter setup on some data. When I perform an advanced filter either in-place or to another location, the auto filter disappears. Is it supposed to be have like that? If so is there some sort of work around?

Ross

Re: Disable Query Refresh Message On Open

Davo,

I have the same issue. The only option I have found was this registry entry on each computer.
http://support.microsoft.com/kb/248204/zh-cn

Ross

Re: Format Entering A "." Instead Of A Zero

Thanks,

That will work.

Ross

Hello all,

when ltoj is less than zero the following code enters a period in the cell instead of zero.

If ltoj < 0 Then ltoj = 0
Cells(r, 10) = Format(ltoj * 24, "##.##") 'writes data in correct format
.....

anyone have any ideas?

Ross

Re: Colouring Cells With Text Entered By A Value In Another Cell

Conditional format

Type this in the conditional format for cell F1
=(E1="Yes")
set the font you desire.

Copy and past the formats down as needed.

HTH,

Ross

Hello all,

this works fine

Sub print_recap()
PrintOut Copies:=1
End Sub

this returns a "sub or function not defined" on the printout line

Sub print_All_recaps()
Set ws = Worksheets("Employees")
If UCase(InputBox("Enter y to print ALL the records")) <> "Y" Then Exit Sub
For n = 2 To ws.Range("A65536").End(xlUp).Row
Cells(2, 2) = ws.Cells(n, 1)
PrintOut Copies:=1
Next
End Sub

If I remove the PrintOut line I do not get an error.

anyone have any ideas?

Thanks,

Ross

Re: Remove (blank) From Pivot Table

Thanks bryce - I never thought about right-click. I guess I was busy looking through all the options.

Hello all,

I've attached a sample file.

Really need to remove the (blanks) in all my headings.
Download sample and let me know if you have any ideas.

Tia,
Ross

Hello all,

my code is modified from the thread below.
http://www.ozgrid.com/forum/showthread.php?t=19401

Sub createWordReport()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range


Dim Fname As Excel.Range
Dim Mname As Excel.Range
Dim Lname As Excel.Range


With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With


Set myDoc = "C:\Documents and Settings\User\My Documents\test.doc"


Set Fname = Sheets("testing").Range("A1")
Set Mname = Sheets("testing").Range("A2")
Set Lname = Sheets("testing").Range("A2")


With myDoc.Bookmarks


.Item("Fname").Range.InsertAfter First
.Item("Mname").Range.InsertAfter Mid
.Item("Lname").Range.InsertAfter LAst


End With


errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing


End Sub

I already have the word like created and the bookmarks added.
I recieve an error "User defined type not defined" on this line.

Dim wdApp As Word.Application

I have a word document created with bookmarks (First, Mid, Last) in it already
Ive tried it with my word file open as well as closed and still no luck.

Anyone have any ideas?

Thanks,

Ross

Re: Filter Text File While Importing

Andy,

On a different computer I downloaded the 2 sample files from the thread and they work fine. But not on my regular computer. I will investigate further.

Can you elaborate further on your statement.
"Although your .txt file does not match the contents of the excel file. Should it?"

Ross

Re: Filter Text File While Importing

Andy,

Sorry - you are correct, test data does not produce the problem.
Attached is my excel file and a test.txt file that do produce the problem.

Thanks for looking at it.

Ross