Macro Help for a Complete Novice!

[The Gent]

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.

 

[The Gent]

Help...?

 

[jmacleary]

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:

application.CalculateUntilAsyncQueriesDone

 

[The Gent]

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:

application.CalculateUntilAsyncQueriesDone

Great thanks.

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

Regards.

 

[jmacleary]

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?

 

[The Gent]

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​

 

[The Gent]

Can anybody shed some light on this?

Regards.