Beginner to VBA

[gdmeckes]

Hello,

I've been creating Macros for Excel for the past couple of months, and I have an error in one of them that I need to fix, which has brought me to the VBA code world. Where is the best place/site to try to begin learning to write and edit macros in VBA?

 

[My Aswer Is This]

Look around here and you see a lot of scripts you can copy and learn from. If you need help with your current script post it here and we will look at it and see if we can help you. Give some specific details on what you want this script to do.

 

[gdmeckes]

Okay, thank you. Here's the thing my data I upload from different machines, and the number of rows is always changing. How can I make it select the number of rows based on the rows in any sheet. I used ctrl+shift+down when I recorded the macro, then when I used the formula to round the original value in previous column I double click to fill down. Should I attach an excel file with the macro or just the code?

 

[My Aswer Is This]

Just show us your code for now.
And: Give some specific details on what you want this script to do.

 

[gdmeckes]

See the note where I added the comment.

Sub PayloadDetailInsertColumnstoRound()
'
' PayloadDetailInsertColumnstoRound Macro
'
'This is a comment area

    ActiveWindow.SmallScroll ToRight:=-1
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Loads"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B4202")
    Range("B2:B2202").Select
    Columns("E:E").Select
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0"
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.SmallScroll Down:=-28
    ActiveWindow.SmallScroll ToRight:=-9
    Range("F1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Range("I1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J1").Select
    ActiveSheet.Paste
    Range("K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L1").Select
    ActiveSheet.Paste
    Range("M1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("N1").Select
    ActiveSheet.Paste
    Range("O1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P1").Select
    ActiveSheet.Paste
    Range("R1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=-9
    Range("G1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Travel Empty Time Round"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Stopped Empty Time Round"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Load Time Round"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Stopped Loaded Time Round"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Loaded Travel Time Round"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Cycle Time Round"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]*(86400/30),0)/(86400/30)"
    
    'This is where the range messes up at I need for it to only shift down to however many rows there are
    'not how many there were in the first sheet i recorded the macro in
    
    
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G2202")
    Range("G2:G2202").Select
    Selection.NumberFormat = "mm:ss"
    Range("G2").Select
    Selection.Copy
    Range("J2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("J2:J2202")
    Range("J2:J2202").Select
    Range("J2").Select
    Selection.Copy
    Range("L2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("L2:L2202")
    Range("L2:L2202").Select
    Range("L2").Select
    Selection.Copy
    Range("N2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("N2:N2202")
    Range("N2:N2202").Select
    Range("N2").Select
    Selection.Copy
    Range("P2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("P2:P2202")
    Range("P2:P2202").Select
    Range("P2").Select
    Selection.Copy
    Range("S2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("S2:S2202")
    Range("S2:S2202").Select
    ActiveWindow.SmallScroll ToRight:=3
    ActiveWindow.SmallScroll Down:=-148
    Selection.NumberFormat = "h:mm:ss"
    ActiveWindow.SmallScroll Down:=-216

 

[Michael M]

Maybe this....UNTESTED

Sub PayloadDetailInsertColumnstoRound()
Dim lr As Long

Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("B1").Value = "Loads"
Range("B2:B4202").Value = "1"
Columns("E:E").NumberFormat = "0"
Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("S:S").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Copy Range("G1")
Range("I1").Copy Range("J1")
Range("K1").Copy Range("L1")
Range("M1").Copy Range("N1")
Range("O1").Copy Range("P1")
Range("R1").Copy Range("S1")
Range("G1").Value = "Travel Empty Time Round"
Range("J1").Value = "Stopped Empty Time Round"
Range("L1").Value = "Load Time Round"
Range("N1").Value = "Stopped Loaded Time Round"
Range("P1").Value = "Loaded Travel Time Round"
Range("S1").Value = "Cycle Time Round"
lr = Cells(Rows.Count, "F").End(xlUp).Row
Range("G2:G" & lr).Formula = "=ROUND(F2*(86400/30),0)/(86400/30)"
Range("G2:G" & lr).NumberFormat = "mm:ss"
Range("G2").Copy Range("J2:J" & lr)
Range("J2").Copy Range("L2:L" & lr)
Range("L2").Copy Range("N2:N" & lr)
Range("N2").Copy Range("P2:P" & lr)
Range("P2").Copy Range("S2:S" & lr)
Range("S2:S" & lr).NumberFormat = "h:mm:ss"
End Sub

 

[gdmeckes]

It doesn't recognize the lr = it says variable not defined.

 

[gdmeckes]

I changed it to LR and it worked great. However, we missed one part of the range to add loads. I changed it to this and I get an error 1004. Also, how can I add this code to an existing worksheet with all of my macros?

Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("B1").Value = "Loads"
Range("B2:B" & LR).Value = "1"
Columns("E:E").NumberFormat = "0"
Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("B1").Value = "Loads"
Range("B2:B4202").Value = "1"
Columns("E:E").NumberFormat = "0"

 

[Michael M]

those lines are already in the code....do you mean you need them in there 3 times ???

 

[gdmeckes]

No, you changed all of the other columns to only write data down to the farthest data point in, but there was one more line up at the top of the beginning of the code that had the (B2:B4202). I changed it to match what you changed on the other ones to B2:B & LR and I got the error code.