|
| |
|
|
steveg
Posts: 309
Joined: 10/20/2004
Status: offline
 |
Excel Query Needed - 8/20/2009 12:39:58
I need some help.
I receive lots of files that contain rows of data. The last column for the data is always column y.
In some of my files I need to find the cell that is in column Y in the last row of data and if it is empty I need to add A1 into the cell. If it already contains data I do not need to add A1.
Although Column Y in the last row may not contain data, there will be data in a number of the other cells in the last row.
I am not really an Excel person so all help is appreciated.
Steve
< Message edited by steveg -- 8/20/2009 13:54:55 >
|
|
|
|
treetopsranch
Posts: 1539
From: Cottage Grove, OR, USA
Status: offline
|
RE: Excel Query Needed - 8/20/2009 16:09:05
Have you looked at the IF logical function?
_____________________________
Don from TreeTops Ranch, Oregon
"I've got a taste for quality and luxury"
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Excel Query Needed - 8/21/2009 8:17:48
Your gonna have to use VBA to accomplish it. Then just assign it to a macro and you should be set:.
Sub steveg()
Dim lastRow As Long
lastRow = Range("X1").End(xlDown).Row
Dim lastCell As Range
Set lastCell = Cells(lastRow, "Y")
If IsEmpty(lastCell) Then lastCell = Range("A1")
End Sub
If you have a COMPLETE blank row somewhere within your data, it will not work. Partial blank rows will be fine
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
steveg
Posts: 309
Joined: 10/20/2004
Status: offline
|
RE: Excel Query Needed - 8/21/2009 11:49:43
Hi Swoosh
Thanks for that. Looking at the code, (probably because I was not very clear) it will add the content of A1 to last filled row/column y. However, I just wanted to add the text "A1" into the cell. It is in fact an end of file marker.
If there is already content in the last record column y then the end of file marker is not needed.
Do I just remove the range details and leave "A1" to add the text?
Steve
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Excel Query Needed - 8/21/2009 12:38:18
Hey Steve, now reading your original post again you were very clear. I apologize for my misinterpretation. Makes perfect sense for an "end of file" marker".
quote:
Do I just remove the range details and leave "A1" to add the text?
Yes,
Change
If IsEmpty(lastCell) Then lastCell = Range("A1")
TO
If IsEmpty(lastCell) Then lastCell = "A1"
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
steveg
Posts: 309
Joined: 10/20/2004
Status: offline
|
RE: Excel Query Needed - 8/21/2009 12:58:26
Hi again Swoosh
I have implemented the code but although it appears to run, nothing actually happens.
There is always data in some of the cells in every row but as I mentioned, not always in column Y.
Any thoughts?
Steve
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Excel Query Needed - 8/21/2009 13:02:35
I just tried it and seems to work fine even if "Y" doesn't always have data
if last cell of range (Column Y) has data then nothing happens
if last cell of range (Column Y of last data filled row) then text "A1" appears
Is that correct?
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
steveg
Posts: 309
Joined: 10/20/2004
Status: offline
|
RE: Excel Query Needed - 8/21/2009 13:05:37
That is exactly how it should work.
When I run it on my file, I don't get an error for the macro, I just don't get the A1 in column Y of the last data row?
Let me delete it and re-create it and try again.
By the way, thank you for your time and patience, it is greatly appreciated.
Steve
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Excel Query Needed - 8/21/2009 13:06:38
PM me and I'll give you my email so you can send it to me........unless it is sensitive data that I shouldn't see.
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
swoosh
Posts: 1535
Joined: 5/18/2002
From: Beaver Falls, PA
Status: offline
|
RE: Excel Query Needed - 8/23/2009 21:52:29
Steve, it won't work because column X could have a blank value in the last row. Column A more than likely would not correct? If so, then the following will work: Notice the reference to cell A1 in place of the old X1
quote:
Sub steveg()
Dim lastRow As Long
lastRow = Range("A1").End(xlDown).Row
Dim lastCell As Range
Set lastCell = Cells(lastRow, "Y")
If IsEmpty(lastCell) Then lastCell = "A1"
End Sub
_____________________________
Swoooosh
Just Do It!
| |
|
|
|
steveg
Posts: 309
Joined: 10/20/2004
Status: offline
|
RE: Excel Query Needed - 8/31/2009 16:44:56
Hi Swoosh
Tried it, works perfectly.
Thank you so much!
Steve
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 