Results 1 to 7 of 7

Thread: Macro Help for a Complete Novice!

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro Help for a Complete Novice!

    Hi guys,

    Macro basic level user here.

    I have created a macro but I need some help to get it a bit slicker as I have bolted it together like lego. I would also like the macro to repeat by looking at a list as opposed to me running it 200+ times.

    Current code:

    Sub NEXTINLIST()
    Dim v As Variant
    Windows("Ecc vs S4.xlsx").Activate
    With Sheets("Reconciliation").Range("B4")
    If .Value = "" Then
    .Value = Sheets("Locations").Range("A2").Value
    Else
    v = Application.Match(.Value, Sheets("Locations").Range("A2:A263"), 0)
    If IsNumeric(v) Then
    .Value = Sheets("Locations").Range("A2:A263").Cells(v + 1, 1).Value
    Else
    .Value = ""
    End If
    End If
    '
    Windows("Ecc vs S4.xlsx").Activate
    Range("G1:G4").Select
    Selection.Copy
    Windows("Reconciliation 2 Progress Tracker.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    Selection.Offset(1, 0).Select
    '
    End With
    End Sub


    Further info:
    • 'Locations' sheet has a list of 262 company's that I want the macro to look up to from C2:C63. I want the macro to repeat until it gets to the bottom of this list.
    • After B4 in 'Reconciliation' has been selected the sheet needs to refresh to pull figures from a database. This is why I can't loop the macro at the moment, as the macro continues whilst the database is still refreshing.

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Help for a Complete Novice!

    Help...?

  3. #3
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    637
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Help for a Complete Novice!

    Hi there. Can't do much with this at the moment (too busy with work work), but if you need to wait until refreshes are done, put this line in and it will stop at that point until all refreshes are complete:

    Code:
    application.CalculateUntilAsyncQueriesDone
    Last edited by jmacleary; Jul 23rd, 2019 at 10:53 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Help for a Complete Novice!

    Quote Originally Posted by jmacleary View Post
    Hi there. Can't do much with this at the moment (too busy with work work), but if you need to wait until refreshes are done, put this line in and it will stop at that point until all refreshes are complete:

    Code:
    application.CalculateUntilAsyncQueriesDone
    Great thanks.

    Any suggestions on how to get this to loop until reaching the bottom of a list?

    Regards.

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    637
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Help for a Complete Novice!

    I will try to look at it tonight - it won't be difficult. Can you spell out in a bit more detail which bits need to loop, and where the results will go to?
    Last edited by jmacleary; Jul 23rd, 2019 at 11:12 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Help for a Complete Novice!

    Quote Originally Posted by jmacleary View Post
    I will try to look at it tonight - it won't be difficult. Can you spell out in a bit more detail which bits need to loop, and where the results will go to?
    Sure...

    Current code:

    Sub NEXTINLIST()
    Dim v As Variant
    Windows("Ecc vs S4.xlsx").Activate
    With Sheets("Reconciliation").Range("B4")
    The above location currently looks to a data validation list, this is the list that I want the macro to run through until it reaches the bottom of the list.

    If .Value = "" Then

    .Value = Sheets("Locations").Range("A2").Value
    List is in the above location an spans from A2:A263, i.e. the macro should run 262 times.

    Else

    v = Application.Match(.Value, Sheets("Locations").Range("A2:A263"), 0)
    If IsNumeric(v) Then
    .Value = Sheets("Locations").Range("A2:A263").Cells(v + 1, 1).Value
    Else
    .Value = ""
    End If
    End If
    '
    I think the suggested "wait for refresh" command should go here. Before being copied and pasted into another worksheet as below. Currently on selected cell in the other worksheet worksheet but would prefer it pointed to '2018' B3.

    Windows("Ecc vs S4.xlsx").Activate
    Range("G1:G4").Select
    Selection.Copy
    Windows("Reconciliation 2 Progress Tracker.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    Selection.Offset(1, 0).Select
    '
    End With
    End Sub

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Help for a Complete Novice!

    Can anybody shed some light on this?

    Regards.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •