Where to find syntax 2

[GreenTeeth]

Can anyone point me at a site that can give me basic syntax for VBA, cos at the moment every time I do something, I have to come here for the answer.

I'm stuck again, on just trying to scroll to the left most column.

I got
Range(A53).Select

when I don't want to go to row 53, I want to stay on the current row but go to column A

But to be honest a website that could clear some of this up would probably be more usefull.

Any help much appreciated

Regards
Rob

When student asks a questions, often the most obvious solution is the answer.

 

[SkipVought]

hey green

Check out this faq707-4105 How Can I Make My Code Run Faster?

In particular, for some reason you have selected A53.

It is the ActiveCell. Unless you ABSOLUTELY had to have selected it, you could ALSO have refrenced it in your code as Range("A53&quot or Cells(53, 1) or Cells(53, "A&quot

So we have a reference point -- and now we want to reference the first column in the referenced row? Well you're ALREADY THERE!

You want to select the LAST column of data in that row?

With ActiveSheet.UsedRange
  Cells(53, .Columns.Count + .Column - 1).Select
End With

Still don't know what you're trying to do





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[GreenTeeth]

Thanks for your help, it all helps I am just trying to learn VBA starting with excel, because I am in a boring data input job at the moment. So I have been writing small macros to speed up the monotony that do little things like save and close a workbook, and then open another one ready for input at the press of one button.

there are other ones, I just couldn't think of a better way to start learning other than to use the record macro facility and then modify the code to run on any spreadsheet. But that is what I am having the problem with because I don't know how to make the code so it is not specific to just that workbook. I have successfully modifyed about 3 or 4 different ones now all with the help of tek tips, I just thought that there might be like a website somewhere that might just give syntax pointers.

So actually, I am trying to get rid of the 53 and make it so where ever it was saved, it will simply open up on the first first sheet with the cursor or active box in the A column somewhere but not on 53 just on the row it was left on.

When student asks a questions, often the most obvious solution is the answer.

 

[SkipVought]

Well then in the workbook_open event

private sub workbook_open()
   Worksheets(1).Activate
   [A1].Select
end sub

or Range("A1&quot.Select
or Cells(1, 1).Select
or Cells(1, "A&quot.Select



Look around on

http://www.j-walk.com.

Best thing is to look at code snippits and experiment, just as you are doing.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

BTW,

Walkenbach, of

http://www.j-walk.com,

has some of the best Excel VBA reference books I've used.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[GreenTeeth]

Thanks Skip, this is of enormous help to me, thanks for taking the time.

Rob

When student asks a questions, often the most obvious solution is the answer.

 

[DaleWatson123321]

Hi GreenTeeth,

Here's an "assist" to Skip's solution.

I noted from your description that you want the cursor to be on the SAME ROW that the cursor was on when the workbook was last saved.

Therefore you might find the following useful. It's based on there being only the one Worksheet.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
r = ActiveCell.Row
Cells(r, 1).Select
Application.ScreenUpdating = True
End Sub

Scenario #2: You have more than one Worksheet and the user leaves the cursor on a particular row of another Worksheet, and you want the cursor to go to the First Worksheet but to the SAME ROW as the cursor was on in the other Worksheet when the file was saved.

In this case you would need to add one line, as follows...

rivate Sub Workbook_Open()
Application.ScreenUpdating = False
r = ActiveCell.Row
Worksheets(1).Activate
Cells(r, 1).Select
Application.ScreenUpdating = True
End Sub

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GreenTeeth]

Yes, of course, this is most logical, and I had forgotten what programming I had done in the past. Every time someone posts something like this, suddenly it opens up a whole world of capability.

have a star

When student asks a questions, often the most obvious solution is the answer.

 

[boggg1]

I prefer to have a book when I'm learning and there is a VERY cheap book about Visual Basic (although its not SO hot on Excel specific stuff). I get no comission for recommending VISUAL BASIC COMPLETE by Sybex. This 1,000 page book is $20 in the US (so it says) and I got it for £17 or so in the UK and its superb value. There are more exact books but none better value.

Boggg1

 

[VBAjedi]

Hey, Rob

Just wanted to pipe up here and tell you to stick to it! I started exactly where you are - bored with data entry and using the macro recorder to automate routine tasks. When they didn't quite do what I wanted, I waded in to the code and learned how to tweak it. When i got stuck, I turned to this forum. No classes, no books, but I learned!

Two years later, I am at a different company with better pay and the title of "Business Process Analyst". I get to write VBA all day now, and routinely create complex workbook tools that slice and dice data, generate reports, generate emails and documents, and much more. And, I'm getting paid to learn PHP/MySQL/Apache so I can start developing web-based tools for the company too. I'm LOVIN' what I do!

Keep pursuing the things that interest you, and determine to get GOOD at them!

That's my two cents. . .

VBAjedi