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.

the code stops because you're trying to select a range on a sheet that is NOT
active.

The good news is you don't need to select the ranges on which you are
acting. If you assign the range you're trying to select to a range variable,
you should be ok

dim rng as Range
set rng = SHD.Cells(i + 1, "B")
with rng.validation
etc




"Joe" wrote:

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.

Thank you very much. I shall try that - assigning ranges.

Joe.