An "Old Geezer" coming over from the Access Forum

[GraeagleBill]

I don't know if anyone reads these posts but I thought I'd at least mention I've been programming Access apps for quite some time. One of my current apps suddenly needs to populate Excel cells in an Excel form with data gleaned from the apps Access tables. I haven't done anything in Excel since before Office 2003 so the VBA object library will likely look like a foreign language. I'll post specifics as issues arise. I know I need to learn how to find names in a column and also the next available row into which I can enter new data.

Cheers,
Bill

[arlu1201]

Hello GraeagleBill,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

[Doc.AElstein]

Hi Bill,
Once in a while a real person pops by to say Hello to a new member inroducing themselves!
( I have no idea where the arlu1201 message comes from, He/ She has not posted for some time, and I have not yet found anyone who has any info on Him/her).
I have never tried Access. I always thought it was something very advanced that you moved on to once you had mastered Excel.
I handle lots of data ( but all for a personal project ) and had a quick look at alternatives to Excel. I tried to stretch myself, keeping open minded. I did a lot of ADO stuff, SQL , and other “queery” things.

But I went back to using simple Excel Files. In my case I still use combination of using closed workbook references on those files, along with some efficient VBA search routines which I wrote. I found that amongst the most efficient ( and by far the most reliable and understandable at least for me… but I guess if I had been better educated in Access and data bases stuff and all those queery things initially then I might think differently )

Good luck with the Excel
There are a lot of great and generous helpers here.

If you prepare questions carefully, and are patient, then you can usually expect lots of great help.

If you have already been around Forums then you probably know most of the usual Forum rule stuff anyway ( and probably better than me ) , but it does no harm to have a quick look through these rule thingys:
https://www.excelforum.com/forum-rul...rum-rules.html
I think some of us might commit suicide if we have to keep telling new members to use code tags and the such…..


Alan
Aka Dave Allen



Edit P.S. ... while i am here... I am refreshing my knowledge after a break…

.... ... and also the next available row into which I can enter new data....l

Store this info – it might make sense.. at least later if it does not now.. …

Normal data basey ways of adding stuff ( records )
So I guess professional ways of storing and adding data like dictionaries and stuff let you just add stuff directly and it is all done in some super efficient way and no one remembers anymore exactly how it is wired , why it works, and probably never know why it does not work sometimes.…

But one thing I like about doing stuff “manually” in Excel is that you can get at least some understanding of what is going on and then can modify it and have half a chance of fixing it when it goes wrong…

In simple Excel VBA when playing with data in a simple worksheet you often need to know where the next free place is to Add stuff
So lets say you have rows and columns of data. Simple data tables. ( A simple novel concept many very highly qualified database people do not understand as they forgot and just do queery stuff all the time)
Assume a column such as column A will always have something in it, that is to say used rows of data will always have something in column A , even if some other columns my be empty.
So if you know the last row with data in it in column A, then the next row will be where you typically add data.

So you need to find the last used row in column A.

This is the most common way, say applied to any arbitrary first ( Item(1) in the below code ) worksheet looking like this
Using Excel 2007 32 bit

Row\Col	A	B	C	D
1
2		RngItm(1, "A") &(1)	RngItm(1, "B") &(2)	RngItm(1, "C") &(3)
3		RngItm(2, "A") &(10)	RngItm(2, "B") &(11)	RngItm(2, "C") &(12)
4		RngItm(3, "A") &(19)	RngItm(3, "B") &(20)	RngItm(3, "C") &(21)
5	Last used row Incolumn 1	RngItm(4, "A") &(28)	RngItm(4, "B") &(29)	RngItm(4, "C") &(30)
6		RngItm(5, "A") &(37)	RngItm(5, "B") &(38)	RngItm(5, "C") &(39)
7		RngItm(6, "A") &(46)	RngItm(6, "B") &(47)	RngItm(6, "C") &(48)
8		RngItm(7, "A") &(55)	RngItm(7, "B") &(56)	RngItm(7, "C") &(57)
9		RngItm(8, "A") &(64)	RngItm(8, "B") &(65)	RngItm(8, "C") &(66)
10		RngItm(9, "A") &(73)	RngItm(9, "B") &(74)	RngItm(9, "C") &(75)
11
12

Worksheet: Tabelle1

Copy this code:
( see next post or here: https://pastebin.com/1adPpV6J )


Put it in a normal code module ( Hit Alt+F11 and paste the code in the big code window that should come up )
Click anywhere in the code and hit F5 or hit the small play icon
Module1Play.JPG https://imgur.com/XIR6zYy

If your first worksheet has a last entry in column 1 of row 5 , as in that screenshot, then the code should tell you that

This is the important Code line:

= Ws1.Cells.Item(Ws1.Rows.Count, 1).End(xlUp).Row

It gives you the row number of that cell. So _ Lr + 1 _ is your row to add the next data

This is the explanation:
The _ .Cells _ property returns all the cells as a Range object of the object to which it is applied. In this case it returns all the cells in a worksheet ( here the first worksheet ) , as a VBA Range object.
In simple terms, in this case, the Worksheet can then be considered as a single massive rectangular Area of cells, with top left referred to typically as cell A1

The VBA Range object, is probably the most important thing for you to read up on and / or revise.
Any Range object organises its cells into item numbers counting from left to right and then next row down. ( In that screen shot shown in yellow I have coincidentally shown a part ( 3 columns of 9 total columns and 9 total rows ) of another range object. That range object shown in Yellow starts at B2)

.Cells _ applied to a Worksheet returns the range object of the entire cells starting at A1.
We can refer to the Items of any range object in a few ways, - Simplified in these three ways:_
_ (Item number counting left to right then down) __ So A1 would be (1) in our case, and B2 of our entire worksheet cells would be (1) for the range object shown in yellow
_ (row number, column number ) __ with (1, 1) being top left of the entire spreadsheet Area returned by .Cells as in our case. ( For that range shown in yellow B2 of our entire worksheet cells is item (1, 1) in that “yellow” range object )
_ (Row number, column letter ) __ Top left for that returned by .Cells applied to a worksheet will be (1, “A”) as in our case. ( For that range shown in yellow B2 of our entire worksheet cells is item (1, “A”) in that “yellow” range object

In that code line we use the second way (row number , column number)
We are referring to, ( pseudo code )
_ AllWorksheetCellsRangeObjectItem(1048576, 1) __1 for XL 2007+
or
_ AllWorksheetCellsRangeObjectItem(65536, 1) __1 for XL 2003
We are referring to the last cell in column A:

Those big numbers come form _ Ws1.Rows.Count
.Rows Property of a worksheet returns a thingy ( object ) containing stuff ( Methods, Properties ) about all rows of the thing it is applied to ( The worksheet in our case). One of its Property thingys is .Count which gives you the total number of rows. So Rows.Count will give you 1048576 for XL2007+ and 65536 for XL 2003

So _ Ws1.Cells.Item(Ws1.Rows.Count, 1) _ is returning us the cell ( as a range object: a range object can be one or more cells ) of the last cell in column A in terms of (row number , column number)

.End _ is a Property of a range object ( sometimes regarded as a Method by some people ) which is a sort of VBA equivalent of Hitting the _ Ctrl Key + an Arrow Key __ It is sometime considered as a Method as it takes a parameter argument in the ( ) . The parameter argument _ xlUp _ is equivalent to Hitting the ( Ctrl key + Up Arrow Key). So this command .... “takes us to” or “returns to us”or "gives us" or "presents us" or "exposes to us the interface of" ......
____ ..... the range object which is the next cell it finds with something in it, having started from the last cell in the worksheet column A.

Once we have the range object of the last cell down the column with something in it, then we can use the .Row Property of a range object to return the row of the top left cell of that range object Area, ( which for the case of a single cell will be its only row – Top left cell in a singles cells range object is that cell .. I think…. Being uncertain here is not as weird as it might sound. You will learn that you can use those item numbers to refer to cells outside the range object also..and in certain ways using negative numbers… which is a bit weird and confusing… So “Top Left” is probably more correctly referred to as Top left origin (1) or Top left origin (1, 1) or top left origin (1, “A”) – just to be on the safe side… . )



http://www.excelfox.com/forum/showth...eadsheet-cells
https://www.excelforum.com/developme...ml#post4551080
https://powerspreadsheets.com/excel-vba-range-object/
http://www.excelforum.com/tips-and-t...eet-cells.html

[Doc.AElstein]

Code for last post
( Also here https://pastebin.com/1adPpV6J )

Please Login or Register  to view this content.

[GraeagleBill]

Thanks for the reply Alan. I noticed at first exploration of the forum that arlu1201 seemed to chime-in when new users posted an introduction........... sort of suggests an automatic script established by the web-master?

With the app I mentioned in my intro, everything I do regarding Excel will be in the realm of automation. As such, all my actions within worksheets will be done with the sheets invisible to the user and involve searching for data within cells and the assignment of data to cells, all of which done with VBA code and data from Access database queries. A practice of mine, when appropriate, is to post my code where it might find general interest and/or general application.

Thanks again for your reply,
Bill