Access : VBA attaching a Macro to a button

    Hi,


    I attempted to attach the following Macro to a button. I have already run successfully from VBA editor so didn't abticiapate any problems, but when I tried to run it the following:


    Run Time error 1004 Select Method of Range Class Failed.


    And it stoppe don teh line:


    Range("A1").Select


    but it workrs when I go into it in VBE and press run??


    Sub Consolidate()


    Dim x As Single
    Dim y As Double
    Dim n As Integer


    n = 2


    Dim Rows As Single
    Worksheets("SWAPS Data").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    'ActiveWindow.SmallScroll Down:=-1
    Rows = Selection.Cells.Count


    For x = 2 To Rows


    If Cells(x, 19).Value <> "" Then
    If Cells(x, 19).Value <> 0 Then
    trade = Cells(x, 1)
    float = Cells(x, 19)
    Dates = Cells(x, 21)


    Worksheets("Process").Cells(n, 3).Value = float
    Worksheets("Process").Cells(n, 1).Value = Dates
    Worksheets("Process").Cells(n, 2).Value = trade
    Worksheets("Process").Cells(n, 4).Value = "jpy"


    n = n + 1


    End If
    End If
    Next x


    'For x = 2 To Rows


    'If Cells(x, 20).Value <> "" Then
    'If Cells(x, 20).Value <> 0 Then
    'trade = Cells(x, 1)
    'float = Cells(x, 20)
    'Dates = Cells(x, 21)


    'Worksheets("Process").Cells(n, 3).Value = float
    'Worksheets("Process").Cells(n, 1).Value = Dates
    'Worksheets("Process").Cells(n, 2).Value = trade
    'Worksheets("Process").Cells(n, 4).Value = "jpy"
    'n = n + 1


    'End If
    'End If
    'Next x



    For x = 2 To Rows


    If Cells(x, 23).Value <> "" Then
    If Cells(x, 23).Value <> 0 Then
    trade = Cells(x, 1)
    float = Cells(x, 23)
    Dates = Cells(x, 24)


    Worksheets("Process").Cells(n, 3).Value = float
    Worksheets("Process").Cells(n, 1).Value = Dates
    Worksheets("Process").Cells(n, 2).Value = trade
    Worksheets("Process").Cells(n, 4).Value = "jpy"
    n = n + 1


    End If
    End If
    Next x


    For x = 2 To Rows


    If Cells(x, 28).Value <> "" Then
    If Cells(x, 28).Value <> 0 Then
    trade = Cells(x, 1)
    float = Cells(x, 28)
    Dates = Cells(x, 30)


    Worksheets("Process").Cells(n, 3).Value = float
    Worksheets("Process").Cells(n, 1).Value = Dates
    Worksheets("Process").Cells(n, 2).Value = trade
    Worksheets("Process").Cells(n, 4).Value = "usd"


    n = n + 1


    End If
    End If
    Next x


    For x = 2 To Rows


    If Cells(x, 32).Value <> "" Then
    If Cells(x, 32).Value <> 0 Then
    trade = Cells(x, 1)
    float = Cells(x, 32)
    Dates = Cells(x, 33)


    Worksheets("Process").Cells(n, 3).Value = float
    Worksheets("Process").Cells(n, 1).Value = Dates
    Worksheets("Process").Cells(n, 2).Value = trade
    Worksheets("Process").Cells(n, 4).Value = "usd"


    n = n + 1


    End If
    End If
    Next x


    Worksheets("FX Data").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Rows = Selection.Cells.Count


    For x = 2 To Rows


    If Cells(x, 16).Value <> "" Then
    If Cells(x, 16).Value <> 0 Then


    trade = Cells(x, 1)
    'USD = Cells(x, 15)
    JPY = Cells(x, 16)
    Dates = Cells(x, 17)


    'Worksheets("Process").Cells(n, 3).Value = USD
    'Worksheets("Process").Cells(n, 4).Value = "USD"
    Worksheets("Process").Cells(n, 3).Value = JPY
    Worksheets("Process").Cells(n, 4).Value = "jpy"
    Worksheets("Process").Cells(n, 1).Value = Dates
    Worksheets("Process").Cells(n, 2).Value = trade



    n = n + 1


    End If
    End If
    Next x


    For x = 2 To Rows


    If Cells(x, 15).Value <> "" Then
    If Cells(x, 15).Value <> 0 Then


    trade = Cells(x, 1)
    USD = Cells(x, 15)
    'JPY = Cells(x, 16)
    Dates = Cells(x, 17)


    Worksheets("Process").Cells(n, 3).Value = USD
    Worksheets("Process").Cells(n, 4).Value = "usd"
    'Worksheets("Process").Cells(n, 3).Value = JPY
    'Worksheets("Process").Cells(n, 4).Value = "JPY"
    Worksheets("Process").Cells(n, 1).Value = Dates
    Worksheets("Process").Cells(n, 2).Value = trade



    n = n + 1


    End If
    End If
    Next x




    'Rows = ActiveSheet.UsedRange.Rows.Count
    'For x = q
    End Sub



    :mad::mad:

    Excel will sometimes (always?) generate an error when you try to select a cell that is already selected. That may be what's happening for you. It is seldom necessary for a macro to actually select anything (even though the macro recorder shows the selection). For example, in your code you can replace the lines



    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    'ActiveWindow.SmallScroll Down:=-1
    Rows = Selection.Cells.Count


    with the one line


    Rows = Range("A1").End(xlDown).Row

    Hi,


    The macro now runs but Rows no longer finds the last non empty cell in the column instead is just find the bottom of the sheet!


    Also the macro no longer seems to work!!


    The if statement now seems to skip over cells with values in!!!! :(

    I see nothing in your code above that could be causing your new problems by making the change I suggested, assuming there is in fact appropriate data in sheet. You may want to change the type of some of your variables. I suggest the beginning of your macro look like

    Code
    Dim x As Long
Dim y As Double
Dim n As Long
n = 2
Dim Rows As Long
Worksheets("SWAPS Data").Activate
Rows = Range("A1").End(xlDown).Row
For x = 2 To Rows

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login