Solved: Green is the color for newbies: Man am I Green

[YellowLabPro]

As stated in my 3 beginning posts, I have no real experience w/ VBA. So I got a book and starting to read it through.
I was hoping to find some resources either a site w/ how to break into this stuff. A VBA Component List and their definitions would be extremely helpful too.


Thanks,

YLP

[johnske]

Hi YLP,

The best resource is probably your VBA Help files, although they're more of a reference than a training aid. Just the same, go through them and try the examples given.

Look through the VBAX KB (http://vbaexpress.com/kb/login.php) for examples , VBAX resources (http://www.vbaexpress.com/resources.php) for some links
VBAX training (http://www.vbaexpress.com/training/lesson01.htm) and here (http://vbaexpress.com/training/)

If you do a Google search, there's also heaps and heaps of other help available on the web, e.g. to get you started VBA by MS (http://xlvba.3.forumer.com/index.php?showtopic=97), VBA Fundamentals (http://xlvba.3.forumer.com/index.php?showforum=21), Read some articles (http://xlvba.3.forumer.com/index.php?showforum=8). Like I said, there's heaps out there, you just gotta look around.

Regards,
John :)

[YellowLabPro]

Thanks John,
I will look all of the items you suggested over. I followed the advice to record a macro and then go through it to see what was going on. That definitely helped.
You mentioned to use the help files, are you referring to the F1 key within a term, or is there another location that hosts these help files w/ the examples?
Here is the macro that I recorded, and then the macro edited by me. It works, but I am fairly confident that it is bulky.
What could you suggest from here that I could compare it to write better or more efficient code?
Thanks for all your assistance now and previously.


Sub Macro6()
'
' Macro6 Macro
' Macro recorded 5/20/2006 by YellowLabProject
'
'
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Data").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range("C4:E4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range("M4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("AP4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("G2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Fairfax"
Selection.AutoFill Destination:=Range("A2:A4763")
Range("A2:A4763").Select
End Sub




Sub Macro7()
Sheets("TGFF").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range("C4:D4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("D2:E2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range("M4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range("AP4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("Data").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "Fairfax"
Selection.AutoFill Destination:=Range("A2:A4763")
Range("A2:A4763").Select

End Sub

[johnske]

Thanks John,
I will look all of the items you suggested over. I followed the advice to record a macro and then go through it to see what was going on. That definitely helped.
You mentioned to use the help files, are you referring to the F1 key within a term,...Yes, but you can also access it by clicking Help>Microsoft Visual Basic Help


...or is there another location that hosts these help files w/ the examples?
Here is the macro that I recorded, and then the macro edited by me. It works, but I am fairly confident that it is bulky.
What could you suggest from here that I could compare it to write better or more efficient code?Yes, it is bulky. Read "Optimizing Your Code (http://xlvba.3.forumer.com/index.php?showtopic=18)" and note that you should only very rarely need to use "Select" - it's only put in by the macro recorder because the recorder's simply imitating what you do... Just TRY some things and see what happens, you can always go back to what you had before if it doesn't work

[Bob Phillips]

You can also reduce the code a lot to make it more readable.

For instance, the ACtiveWindow.ScrollDown code is juts you moving about the sheet, it is superfluous.

Then, selecting is rarely necessary, so for instance



Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


can be written as



Range(Range("A4"), Range("A4").End(xlDown)).Copy


Do this throughout the code and you will be able to read it better, and maybe understand. Also, check out http://www.mvps.org/dmcritchie/excel/getstarted.htm

[YellowLabPro]

Thanks John and XLD,
Working on it now. XLD, I changed the code per your suggestion.
With this I have received an error: Could you advise me on two items, what is going wrong, and is there a definition list of errors?

Error:
Run-time error '1004':
Paste method of Worksheet class failed

Error Marked in Dark Red below

Sub Macro8()
Sheets("TGFF").Select
Range(Range("A4"), Range("A4").End(xlDown)).Copy
Application.CutCopyMode = False
Sheets("Data").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range(Range("E4"), Range("E4").End(xlDown)).Copy
Application.CutCopyMode = False
Sheets("Data").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range(Range("C4:D4"), Range("C4:D4").End(xlDown)).Copy
Application.CutCopyMode = False
Sheets("Data").Select
Range("D2:E2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range(Range("M4"), Range("M4").End(xlDown)).Copy
Application.CutCopyMode = False
Sheets("Data").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("TGFF").Select
Range(Range("AP4"), Range("AP4").End(xlDown)).Copy
Application.CutCopyMode = False
Sheets("Data").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("Data").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "Fairfax"
Selection.AutoFill Destination:=Range("A2:A4763")
Range("A2:A4763").Select

End Sub

Thanks YLP

[mdmackillop]

Hi YLP,
Your code can be trimmed to something like the following

With Sheets("TGFF")
Range(.Range("A4"), .Range("A4").End(xlDown)).Copy Sheets("Data").Range("B2")
Range(.Range("E4"), .Range("E4").End(xlDown)).Copy Sheets("Data").Range("C2")
Range(.Range("C4:D4"), .Range("C4:D4").End(xlDown)).Copy Sheets("Data").Range("D2")
Range(.Range("M4"), .Range("M4").End(xlDown)).Copy Sheets("Data").Range("F2")
Range(.Range("AP4"), .Range("AP4").End(xlDown)).Copy Sheets("Data").Range("G2")
End With
Sheets("Data").Range("A2:A4763") = "Fairfax"

[YellowLabPro]

MD,
Could you add one more line to the code please?

I was playing w/ this earlier: this would add the column headers at the top. As it is written here, it does not work.

Range("A1:G1") = ("Store"),("Item#"),("Dept."),("Cat."),("Item Description"),("Price"),("Qty.")

e.g.
A1 = Store, B1 = Item#, C1 = Dept., etc...

[mdmackillop]

Range("A1:G1") = Array("Store", "Item#", "Dept.", "Cat.", "Item Description", _
"Price", "Qty.")

[YellowLabPro]

MD,
That worked great, thanks. But how does the code instruct where to put the results?

YLP

[mdmackillop]

The code says that an range (array) of cells is to equal the array of text values. The "Instruct" command is implied and understood by the compiler (as far as I know!)

[YellowLabPro]

Thanks MD for the help throughout.


YLP