Bob R asked in Computers & InternetSoftware · 10 years ago

Excel VBA Programming Code Problem?

I am having some trouble getting this VBA code to work. I am a bit new to VBA, but have some very basic programming knowledge as far as if then else statements are concerned. What I'd like to have happen is for Excel to figure out how many days have elapsed between today and the last time this code was run, and to insert one row per day elapsed. Here is what I have so far:

Option Explicit

Sub Insert_Rows()

Dim x As Date

Dim y As Long

Dim z As Long

x = Date

y = Sheets("Sheet1").Cells(2, 1)

z = DateDiff("d", x, y) + 1

Rows("z:2").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Any ideas or suggestions would be greatly appreciated. Tips are great too. Also: what does "VBA" stand for? I've seen several webpages about VBA mentioned with C++ and other programming languages. I guess It'd be cool to know where the heck this thing came from and the limits of its capabilities.

Bob

2 Answers

Relevance
  • 10 years ago
    Favorite Answer

    I infer that you want to insert the rows above row 2, moving the date in A2 down the number of rows specified by the macro.

    As it stands your DateDiff function is going to result in a negative number, because the 'x' and 'y' values are reversed in the code line. You are subtracting 'today' from the date in A2.

    When selecting rows using a variable you would have to structure your line 8 as:

    Rows(z & ":" & 2).Select

    The following would be the modified code:

    Option Explicit

    Sub Insert_Rows()

    Dim x As Date

    Dim y As Long

    Dim z As Long

    x = Date

    y = Sheets("Sheet1").Cells(2, 1).Value

    z = DateDiff("d", y, x) + 2

    Rows(z & ":" & 2).Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    End Sub

    VBA refers to Visual Basic for Applictions. This is the built Visual Basic designed/modified to be used with the MS Office suite.

  • Kelly
    Lv 4
    4 years ago

    AQuestionMark hit the answer you are looking for right on the money. I even learned something regarding the ShowModal property. I have to say, AQuestionMark knows their stuff.

Still have questions? Get your answers by asking now.