HI,
I have this sub that runs only if I am on the worksheet "OSMI matches".
Is there a way I can modify it so that it is not sensitive to which
worksheet I am on when I try to run it (using the assigned shortcut
key) ? As of now, if I am on another worksheet, it runs until it hits
the data validation part, and then it just stops, giving the error box
"400". I am really confused .. here is the relevent part of the code:
Sub FOSMITRED()
Dim SHS As Worksheet
Dim SHD As Worksheet
Dim c, d As Range
Dim i, j As Variant
Set SHS = Worksheets("rawresults")
Set SHD = Worksheets("OSMI matches")
Set c = SHD.Cells(1, 1)
Set d = SHS.Cells(1, 2)
i = 1
j = 1
Application.ScreenUpdating = False
'reads results from FOSMIT file into rawresults
Call dataread
'Go through column A of OSMI matches worksheet; stop at the first empty
cell;
'Assign i as the row index of that cell
Do While Not IsEmpty(c)
i = i + 1
Set c = c.Offset(1, 0)
Loop
'Fill asterisks in row i and date in row (i+1)
'Define column J in results as "status"
SHD.Range(SHD.Cells(i, 1), SHD.Cells(i, 9)).FormulaR1C1 = "****"
SHD.Cells((i + 1), 1).FormulaR1C1 = "Date & day: "
SHD.Cells((i + 2), 10).FormulaR1C1 = "Status"
'Enter data validation property for date and day ----- PROGRAM RUNS
TILL HERE ----
SHD.Cells(i + 1, "B").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Month"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
SHD.Cells(i + 1, 3).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Date"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
SHD.Cells(i + 1, 4).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Year"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Thanks in advance,
Joe.
Bookmarks