UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> First Blank Cell Of A Row, Access 2010    
 
   
sneakerhead22
post Mar 13 2018, 11:36 AM
Post#1



Posts: 53
Joined: 18-July 17



Hey guys,

Just a quick question here,

Below is some code that opens up my excel sheet (a picture of what it looks like is attached)
as you can see column A has my weekends date but there are formulas to the right, so I need to find the first blank row in column A. Formulas will calculate after my query gets pasted into their respective columns, so the entire row is not blank, only the invoice date column. I thought my code had done this properly but its actually going to row 59 which is the bottom of all my data, do you guys see anything wrong with my code that would cause this?

CODE
    Set Xl = CreateObject("Excel.Application")
    Xl.Visible = True
    Set XlBook = GetObject(MySheetPath)
    XlBook.Windows(1).Visible = True
    Set XlSheet = XlBook.Worksheets(4)
    lastRow = XlSheet.Cells(XlSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    
      
    unknownAmt = DLookup("[NetCharg]", "[tbl by comp data]", "[Comp] = 'Unknown'")
    XlSheet.Cells(lastRow, "M") = unknownAmt   'This goes to eastbay'



Attached File  example.JPG ( 45.44K )Number of downloads: 1

This post has been edited by sneakerhead22: Mar 13 2018, 12:29 PM
Go to the top of the page
 
Jeff B.
post Mar 13 2018, 11:56 AM
Post#2


UtterAccess VIP
Posts: 10,166
Joined: 30-April 10
From: Pacific NorthWet


You posted in the "Access Modules" forum. You might get more responses if you post (what looks like an Excel question) in one of the Excel forums.
Go to the top of the page
 
sneakerhead22
post Mar 13 2018, 11:57 AM
Post#3



Posts: 53
Joined: 18-July 17



This is an Access Module, that utilizes the Excel Object Library and Opens an excel sheet. If you think that still posting it in an excel forum than I can do so.
Go to the top of the page
 
Jeff B.
post Mar 13 2018, 12:04 PM
Post#4


UtterAccess VIP
Posts: 10,166
Joined: 30-April 10
From: Pacific NorthWet


Just a thought, but questions about how Excel works probably will get more response in an Excel forum.
Go to the top of the page
 
projecttoday
post Mar 13 2018, 12:09 PM
Post#5


UtterAccess VIP
Posts: 10,352
Joined: 10-February 04
From: South Charleston, WV


How would you do it if you were doing it manually on the sheet?

I suspect the only way is to search.
Go to the top of the page
 
sneakerhead22
post Mar 13 2018, 12:16 PM
Post#6



Posts: 53
Joined: 18-July 17



Well each row has an invoice date from the last invoice, so its always going to be the next row, I think I could maybe store in a table what row is the next available row and each time update the table?
Go to the top of the page
 
sneakerhead22
post Mar 13 2018, 12:17 PM
Post#7



Posts: 53
Joined: 18-July 17



i will feel bad for double posting though.
Go to the top of the page
 
doctor9
post Mar 13 2018, 12:17 PM
Post#8


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


sneakerhead22,

> there are formulas to the right

First, I -believe- the problem is that if you have a formula in the cell, that means it's technically not empty/unused, which is throwing your code off.

> I need to find the first blank row in column A only not just an entire blank row
Can you clarify what you mean here? Which row in your screenshot would you want to identify? The row beneath 2/13/2018? Or do you want the row that 2/13/2018 is on, because the FL column has a blank cell?

Second, just curious, why are you using this:
CODE
Set XlBook = GetObject(MySheetPath)

Instead of this?
CODE
Set XlBook = Xl.Workbooks.Open(MySheetPath)

Not saying your method is wrong, it's just that I personally open the file from within the Excel application object.

Finally, if you feel like you posted your question in the wrong forum, just use the [! Report] button and let the Administrators know that you'd like the discussion moved. No need to double-post.

Hope this helps,

Dennis
Go to the top of the page
 
sneakerhead22
post Mar 13 2018, 12:28 PM
Post#9



Posts: 53
Joined: 18-July 17



I guess I dont really have a reason as I didnt even know I could utilize your method that way.

Secondly, Column A is Invoice Date. Each week I get 5 or so invoices. I need to find the row under 2/13/2018 which is where the next invoice date will go and my data will get pasted into columns V,W,X,Y,Z,AA,AB,AC,AD.

There are no formulas in column A though, I do think that if there is anything in any columns the row isnt recognized as empty.

I could try to implement the formulas into updating each week rather than a template but that would be quite a bit slower i would assume
Go to the top of the page
 
doctor9
post Mar 13 2018, 12:40 PM
Post#10


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


sneakerhead22,

> I need to find the row under 2/13/2018 which is where the next invoice date will go

Okay, try this:

CODE
Dim lngFirstBlankRow as Long

lngFirstBlankRow = XlSheet.UsedRange.Rows.Count
While XlSheet.Cells(lngFirstBlankRow - 1, 1) = ""
    lngFirstBlankRow = lngFirstBlankRow - 1
Wend

This should set lngFirstBlankRow to the row number of the first blank row in column A.

Hope this helps,

Dennis
This post has been edited by doctor9: Mar 13 2018, 01:10 PM
Reason for edit: Clarified what my suggested code does
Go to the top of the page
 
sneakerhead22
post Mar 13 2018, 04:23 PM
Post#11



Posts: 53
Joined: 18-July 17



Hey man this worked out really great, I did have to do the following

CODE
    lastRow = XlSheet.UsedRange.Rows.Count
    lngLastRow = lastRow
    While XlSheet.Cells(lngLastRow - 1, 1) = ""
    lngLastRow = lngLastRow - 1
    Wend


Once again thanks for the help!! Just finally finished this big project and it feels good
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 09:08 AM