missing string
Board Regular
- Joined
- Dec 20, 2011
- Messages
- 53
I posted up a couple of months ago asking for help with a workbook: https://www.mrexcel.com/forum/excel-questions/1019628-workbook-has-repair-about-25-time-i-open.html
I remade the workbook from scratch as suggested in the thread and deliberately didn't include the macro from the previous book to see if that had an impact. I used the workbook without the macro for a few weeks and I got no error messages when opening it. I put the macro back in today and it came up with the same error message when I re-opened it:
https://imgur.com/a/GzWTJ
So I now know with 100% certainty that something in the macro is causing the error message. However, I'm not that skilled with macros and got some help online with this, so I don't understand what the issue is - any help would be much appreciated.
I remade the workbook from scratch as suggested in the thread and deliberately didn't include the macro from the previous book to see if that had an impact. I used the workbook without the macro for a few weeks and I got no error messages when opening it. I put the macro back in today and it came up with the same error message when I re-opened it:
https://imgur.com/a/GzWTJ
So I now know with 100% certainty that something in the macro is causing the error message. However, I'm not that skilled with macros and got some help online with this, so I don't understand what the issue is - any help would be much appreciated.
Code:
Sub Update()'
' Update Macro
'
Application.ScreenUpdating = False
Sheets("US Dollar").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Euro").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Swiss Franc").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Hong Kong Dollar").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Australian Dollar").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Canadian Dollar").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Danish Krone").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Wait (Now + TimeValue("0:00:02"))
Sheets("Future").Select
LastRow& = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:C" & LastRow)
Range("C8:C" & LastRow).Select
ActiveWorkbook.Worksheets("Future").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Future").AutoFilter.Sort.SortFields.Add Key:=Range( _
"C7:C" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Future").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Sheets("Alternatives").Select
LastRow2& = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C" & LastRow2)
ActiveWorkbook.Worksheets("Alternatives").Sort.SortFields.Add Key:=Range( _
"H3:H" & LastRow2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Alternatives").Sort.SortFields.Add Key:=Range( _
"C3:C" & LastRow2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Alternatives").Sort
.SetRange Range("B2:L" & LastRow2)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Sheets("List").Select
LastRow3& = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:C" & LastRow3)
Range("C8:C" & LastRow3).Select
ActiveWorkbook.Worksheets("List").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("List").AutoFilter.Sort.SortFields.Add Key:=Range( _
"C7:C" & LastRow3), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("List").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Application.ScreenUpdating = True
End Sub